topic Re: Create sequence and trigger... in Operating System - HP-UX

Create sequence and trigger...

Ratzie — Fri, 18 Apr 2008 20:19:38 GMT

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;

Re: Create sequence and trigger...

Yogeeraj_1 — Sat, 19 Apr 2008 04:59:10 GMT

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

Re: Create sequence and trigger...

Yogeeraj_1 — Sat, 19 Apr 2008 05:00:40 GMT

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

Re: Create sequence and trigger...

Yogeeraj_1 — Sat, 19 Apr 2008 05:01:43 GMT

attached a more readable version.

Re: Create sequence and trigger...

Yogeeraj_1 — Sat, 19 Apr 2008 05:03:32 GMT

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