Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
cancel
Showing results for 
Search instead for 
Did you mean: 

Create sequence and trigger...

Ratzie
Super Advisor

Create sequence and trigger...

Oracle 10g
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;
4 REPLIES
Yogeeraj_1
Honored Contributor

Re: Create sequence and trigger...

Hi,

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Yogeeraj_1
Honored Contributor

Re: Create sequence and trigger...

hi again,

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Yogeeraj_1
Honored Contributor

Re: Create sequence and trigger...

attached a more readable version.
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Yogeeraj_1
Honored Contributor

Re: Create sequence and trigger...

hi again,

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)