-
Setting Counters in Triggers
Hi,
I am starting to work with pl/sql and my job was to identify changes/modifications done to the vendor name. I created a trigger to do just that and the following script seem to work for that.
My problem is I need to set a counter to the vendor_audit table. Meaning everytime an insert/update/delete happens a number is generated for that row. Can someone please assist on how should I go about doing that in this script.
CREATE or REPLACE TRIGGER vendor_trail
AFTER INSERT OR UPDATE OR DELETE ON vendor
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
oper varchar2(8);
ruid number;
name varchar2(64);
name_change varchar2(64);
BEGIN
if inserting then
oper:='insert';
end if;
if updating then
oper:='update';
end if;
if deleting then
oper:='delete';
end if;
/*store previous value into vendor_audit*/
ruid:=ld.ruid;
name:=ld.name;
name_change:=:new.name;
insert into vendor_audit
values(ruid,name,oper,sysdate,name_change);
END;
Thanks.
-
-
Hi Tabreaz,
Thank you for your response.
Appreciate if you could tell me if the sequence should be created as a seperate trigger or can I do that within the same trigger script that I posted?
If on this script itself, should I include anything at the DECLARE portion?
Thanks.
-
A sequence is a separate object created via the CREATE SEQUENCE command. Then you can access the CURRVAL and NEXTVAL pseudo columns within SQL queries directly.
For example :
Code:
rbaraer@Ora10g> Create sequence seq_vendoraudit MAXVALUE 9999999999999999999 CYCLE START WITH 1 INCREMENT BY 1 CACHE 20;
Sequence created.
rbaraer@Ora10g> create table test(id1 number);
Table created.
rbaraer@Ora10g> insert into test(id1) values (seq_vendoraudit.CURRVAL);
insert into test(id1) values (seq_vendoraudit.CURRVAL)
*
ERROR at line 1:
ORA-08002: sequence SEQ_VENDORAUDIT.CURRVAL is not yet defined in this session
rbaraer@Ora10g> insert into test(id1) values (seq_vendoraudit.NEXTVAL);
1 row created.
rbaraer@Ora10g> select * from test;
ID1
----------
1
rbaraer@Ora10g> insert into test(id1) values (seq_vendoraudit.CURRVAL);
1 row created.
rbaraer@Ora10g> select * from test;
ID1
----------
1
1
rbaraer@Ora10g> insert into test(id1) values (seq_vendoraudit.NEXTVAL);
1 row created.
rbaraer@Ora10g> select * from test;
ID1
----------
1
1
2
rbaraer@Ora10g>
HTH & Regards,
rbaraer
-
Hi RBARAER
Thank you for showing me how to create the sequence....was very helpful.
I have included that into the script but I realised my script earlier was wrong, I cannot capture a insert or delete operation. So I changed it as below:
1 CREATE or REPLACE TRIGGER vnd_trail
2 AFTER INSERT OR UPDATE OR DELETE ON vnd
3 REFERENCING NEW AS NEW OLD AS OLD
4 FOR EACH ROW
5 DECLARE
6 oper varchar2(8);
7 ruid number;
8 name varchar2(64);
9 name_change varchar2(64);
10 ctr number;
11 BEGIN
12 case
13 when inserting then
14 oper := 'insert';
15 ruid := NULL;
16 name:=ld.name;
17 name_change:=:new.name;
18 when updating then
19 oper := 'update';
20 ruid:=ld.ruid;
21 name:=ld.name;
22 name_change:=:new.name;
23 when deleting then
24 oper := 'delete';
25 ruid:=ld.ruid;
26 name:=ld.name;
27 name_change:=:NULL;
28 else
29 null;
30 end case;
31 SELECT vnd_audit_seq.nextval INTO ctr FROM dual;
32 insert into vendor_audit
33 values(ruid,name,oper,sysdate,name_change,ctr);
34* END;
35 /
Warning: Trigger created with compilation errors.
SQL> show errors;
Errors for TRIGGER VND_TRAIL:
LINE/COL ERROR
-------- -----------------------------------------------------------------
8/6 PLS-00103: Encountered the symbol "CASE" when expecting one of
the following:
begin declare exit for goto if loop mod null pragma raise
return select update while
<<
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall
I seem to be getting the above error. Appreciate if you could help tell me where I have gone wrong and what should I do.
Thanks a lot.
-
I almost never use triggers (very few exceptions put apart, I hate triggers) so I won't help you on the trigger syntax .
However, you can replace
Code:
SELECT vnd_audit_seq.nextval INTO ctr FROM dual;
insert into vendor_audit
values(ruid,name,oper,sysdate,name_change,ctr);
with
Code:
insert into vendor_audit
values(ruid,name,oper,sysdate,name_change,vnd_audit_seq.nextval);
Furthermore I feel like their are a few typos in your code, such as :
Code:
name_change:=:NULL;
HTH & Regards,
rbaraer
-
Hi RBARAER,
The error that I was getting was apparently due to the Oracle version that I was on...I am still on 8i and case statements it seems only work for 9i. I changed the codes to IF ELSE and it works.
Thank you very much for taking your time to answer my queries...appreciate that very much.
Regards.
-
btw the line
REFERENCING NEW AS NEW OLD AS OLD
doesn't do anything. They optional REFERENCING construction is provided in case you wish to reference NEW and OLD as something different, which for English speakers and probably everyone else is not a good idea anyway.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|