Setting Counters in Triggers
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Setting Counters in Triggers

  1. #1
    Join Date
    Jun 2006
    Posts
    12

    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.

  2. #2
    Join Date
    Sep 2005
    Posts
    278
    use sequence

  3. #3
    Join Date
    Jun 2006
    Posts
    12
    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.

  4. #4
    Join Date
    May 2005
    Location
    France
    Posts
    34
    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

  5. #5
    Join Date
    Jun 2006
    Posts
    12
    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.

  6. #6
    Join Date
    May 2005
    Location
    France
    Posts
    34
    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;
    Code:
    * END;
    HTH & Regards,

    rbaraer

  7. #7
    Join Date
    Jun 2006
    Posts
    12
    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.

  8. #8
    Join Date
    Jun 2005
    Location
    London, UK
    Posts
    159
    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
  •  


Click Here to Expand Forum to Full Width