- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Create sequence and trigger...
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-18-2008 01:19 PM
тАО04-18-2008 01:19 PM
Create sequence and trigger...
I have a table that needs two columns to auto populate.
One with a sequence which I think I have but the other column I need to auto populate with date and time.
Can I combine the two into one trigger, if not how do I create the trigger for the date?
****
create sequence ADVXRT_ODB.SEQ_EAIERRJNLSEQN
START WITH 000000000000001
MAXVALUE 999999999999999
increment by 1
CYCLE
CACHE 20
ORDER;
CREATE OR REPLACE TRIGGER ADVXRT_ODB.INS_TRANSACTIONAUDIT_EAI
BEFORE INSERT
ON ADVXRT_ODB.TRANSACTIONAUDIT
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
SELECT ADVXRT_ODB.SEQ_EAIERRJNLSEQN.NEXTVAL INTO :NEW.EAIERRJNLSEQN FROM dual;
END;
****
I thought this would work for the date...
CREATE OR REPLACE TRIGGER ADVXRT_ODB.INS_TRANSACTIONAUDIT_ROWCREH
BEFORE INSERT
ON ADVXRT_ODB.TRANSACTIONAUDIT
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
:NEW.ROWCREW :=SYSDATE;
END;
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-18-2008 09:59 PM
тАО04-18-2008 09:59 PM
Re: Create sequence and trigger...
You could do that in a single trigger and also referencing of the sequence value could be achieved at the procedure/package level.
NB. it is advisable to include all the logic in a package and call it from the trigger than to include it into the trigger itself...
>auto-populate with date and time
Will it be sysdate?
revert!
kind regards
yogeeraj
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-18-2008 10:00 PM
тАО04-18-2008 10:00 PM
Re: Create sequence and trigger...
Allow me to demonstrate how i would go about implementing something similar.
yogee@mydb.mu>create table tab1 (val1 number(2));
Table created.
Elapsed: 00:00:00.08
yogee@mydb.mu>create table audit_table(audit_date date default sysdate,audit_col1 number(2));
Table created.
Elapsed: 00:00:00.06
yogee@mydb.mu>create sequence myseq
2 START WITH 1
3 MAXVALUE 10
4 increment by 1
5 CYCLE
6 noCACHE
7 order;
Sequence created.
Elapsed: 00:00:00.03
yogee@mydb.mu>create or replace PACKAGE BODY pkg1 IS
2 procedure sproc_transaction_audit is
3 begin
4 insert into audit_table(audit_date,audit_col1) values(sysdate,myseq.nextval);
5 end;
6 END;
7 /
Package body created.
Elapsed: 00:00:00.07
yogee@mydb.mu>create or replace TRIGGER TRG_AFT_INSTAB1_1
2 AFTER INSERT
3 ON TAB1 REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW
4 BEGIN
5 pkg1.sproc_transaction_audit;
6 END;
7 /
Trigger created.
Elapsed: 00:00:00.13
yogee@mydb.mu>insert into tab1 values(1);
1 row created.
Elapsed: 00:00:00.05
yogee@mydb.mu>select * from audit_table;
AUDIT_DATE AUDIT_COL1
________________ __________
19/04/2008 09:49 1
Elapsed: 00:00:00.04
yogee@mydb.mu>insert into tab1 values(2);
1 row created.
Elapsed: 00:00:00.03
yogee@mydb.mu>select * from audit_table;
AUDIT_DATE AUDIT_COL1
________________ __________
19/04/2008 09:49 1
19/04/2008 09:52 2
Elapsed: 00:00:00.07
yogee@mydb.mu>rollback;
Rollback complete.
Elapsed: 00:00:00.03
yogee@mydb.mu>insert into tab1 values(3);
1 row created.
Elapsed: 00:00:00.03
yogee@mydb.mu>select * from audit_table;
AUDIT_DATE AUDIT_COL1
________________ __________
19/04/2008 09:53 3
Elapsed: 00:00:00.04
yogee@mydb.mu>select * from tab1;
VAL1
__________
3
Elapsed: 00:00:00.06
yogee@mydb.mu>
hope this helps!
kind regards
yogeeraj
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-18-2008 10:01 PM
тАО04-18-2008 10:01 PM
Re: Create sequence and trigger...
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-18-2008 10:03 PM
тАО04-18-2008 10:03 PM
Re: Create sequence and trigger...
sorry. my demo does not include this portion:
create or replace PACKAGE pkg1 IS
procedure sproc_transaction_audit;
END;
sorry for inconveniences.
kind regards
yogeeraj