Home » SQL & PL/SQL » SQL & PL/SQL » Trigger sequence problem
Trigger sequence problem [message #39151] Thu, 20 June 2002 22:17 Go to next message
Lucas Kan
Messages: 2
Registered: June 2002
Junior Member
Hi,
I'm a newbie in PL/SQL and I have a problem with triggers. I hope someone is able to point me in the right direction.

Overview:
There is a record table and a backup table. One user may have multiple entries in the same table identified by a ID number. Whenever there is a change in the record table, be it insert/delete/update, the backup table will delete all records related to the user, and insert the new set of records all over again obtained from the updated record table.

Action taken:
3 triggers are at work here.
Trigger 1 is a row-level trigger before the action taken. It records the user's ID number via a variable declared in a Package.
Trigger 2 is a table-level trigger before the action taken. It deletes all records in the backup table with the ID number obtained in Trigger 1.
Trigger 3 is a table-level trigger after the action taken. It inserts all records from record table into the backup table with the ID number obtained in Trigger 1.

Problem:
Both trigger 1 and 2 takes place before the action, which I have no control over their firing sequence. They cannot be combined becoz' I need the user ID number from the ':new' variable, which is not available at table-level trigger.

Therefore is there any way for me to store the ID number before Trigger 2 is fired? Or is my whole approach to this problem wrong?

Thanks.
Re: Trigger sequence problem [message #39154 is a reply to message #39151] Fri, 21 June 2002 05:14 Go to previous messageGo to next message
vipin
Messages: 40
Registered: May 2002
Member
Hi,

I feel one trigger defined on the record table after insert/update/delete can do an insert into backup table. The trigger (after insert) on backup table can do a delete and Insert. Userid is anyhow passed when you insert into backup table.

Hope there is some light here.

vipin
Re: Trigger sequence problem [message #39157 is a reply to message #39151] Fri, 21 June 2002 08:57 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
Correct me if I'm wrong, but it sounds like you just want to keep the two tables in sync. Just wondering, if any change to your record table changes the backup table, how is the backup table _really_ a backup? How would the data in the backup table ever be useful to you?

In any case, as Vipin mentions, I think this would be much easier with just a single after row trigger on record.

if inserting then
  insert into backup...
elsif updating then
  update backup...
   where id = :new.id;
elsif deleting then
  delete from backup where id = :old.id;
end if;


This way you only have one trigger to manage and you reduce the amount of activity - a change to one row in record only changes one row in backup.
Re: Trigger sequence problem [message #39202 is a reply to message #39151] Tue, 25 June 2002 22:47 Go to previous message
Lucas Kan
Messages: 2
Registered: June 2002
Junior Member
Hi there,
Thanks for your help.

Actually the 2 tables are used for different applications. The first table does things normally, while the second table has to delete all existing records and insert the latest set (eg. A,B,C existing. Add D. Delete A,B,C. Insert A,B,C,D.) The problem was that if I use the 'AFTER' trigger, I cannot differentiate which is the record modified.

Anyway I have already solved the problem by creating a Package with a set of variables. The variables will hold the data to be modified using a 'BEFORE' trigger. Later when I require the data, I just retrieve it from the Package variable.

Thanks for your reply!
Lucas.

-----
Correct me if I'm wrong, but it sounds like you just want to keep the two tables in sync. Just wondering, if any change to your record table changes the backup table, how is the backup table _really_ a backup? How would the data in the backup table ever be useful to you?
In any case, as Vipin mentions, I think this would be much easier with just a single after row trigger on record.

if inserting then insert into backup...elsif updating then update backup... where id = :new.id;elsif deleting then delete from backup where id = :old.id;end if;
This way you only have one trigger to manage and you reduce the amount of activity - a change to one row in record only changes one row in backup.
Previous Topic: Re: How to get the primary key value from a new inserted record?
Next Topic: Can you use an Index in the definition or use of a view ?
Goto Forum:
  


Current Time: Sun Oct 20 09:45:20 CDT 2019