Operating System - HP-UX
1748239 Members
3558 Online
108759 Solutions
New Discussion юеВ

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 4
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)