Home » SQL & PL/SQL » SQL & PL/SQL » Trigger for INSERT, UPDATE OR DELETE (10g)
icon5.gif  Trigger for INSERT, UPDATE OR DELETE [message #590219] Mon, 15 July 2013 22:10 Go to next message
ora_dccc
Messages: 2
Registered: July 2013
Junior Member
Hi all,

I currently hv 3 triggers. One for insertion, update and deletion.

How do i merge these triggers into one?

CREATE OR REPLACE TRIGGER ins_linkivr
AFTER INSERT OR UPDATE on LINKIVR
FOR EACH ROW
BEGIN
INSERT INTO linkivr@REL5B (primarykey,prompt,remark)
VALUES (:NEW.primarykey, :NEW.prompt,:NEW.remark);

--------------------------------------------------

CREATE OR REPLACE TRIGGER upd_linkivr
AFTER UPDATE on LINKIVR
FOR EACH ROW
BEGIN
UPDATE linkivr@REL5B set prompt=:NEW.prompt,
remark=:NEW.remark where primarykey=:OLD.primarykey;
END;

------------------------------------------------------

CREATE OR REPLACE TRIGGER del_linkivr
AFTER DELETE on LINKIVR
FOR EACH ROW
BEGIN
DELETE linkivr@REL5B where primarykey=:OLD.primarykey;
END;
Re: Trigger for INSERT, UPDATE OR DELETE [message #590220 is a reply to message #590219] Mon, 15 July 2013 22:12 Go to previous messageGo to next message
BlackSwan
Messages: 22845
Registered: January 2009
Senior Member
>How do i merge these triggers into one?
What is the advantage to combine them?

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: Trigger for INSERT, UPDATE OR DELETE [message #590222 is a reply to message #590220] Mon, 15 July 2013 23:01 Go to previous messageGo to next message
ora_dccc
Messages: 2
Registered: July 2013
Junior Member
I've managed to merge it.

CREATE OR REPLACE TRIGGER sync_linkivr
AFTER INSERT OR UPDATE OR DELETE on LINKIVR
FOR EACH ROW
BEGIN
	IF INSERTING THEN
       INSERT INTO linkivr@REL5B (primarykey,prompt,remark)
       VALUES (:NEW.primarykey, :NEW.prompt,:NEW.remark);
	END IF;
    IF UPDATING THEN
	   UPDATE linkivr@REL5B set prompt=:NEW.prompt, 
       remark=:NEW.remark where primarykey=:OLD.primarykey;
    END IF;
	IF DELETING THEN
	   DELETE linkivr@REL5B where primarykey=:OLD.primarykey;
END IF;
END;


I understand that triggers should be the last resort. Especially in this case
where a remote table needs to be synced with the master table.

I have read that insert and update could be in one procedure and delete should be in the other. Any comments on that?

*BlackSwan added {code} tags. Please do so yourself in the future

[Updated on: Mon, 15 July 2013 23:08] by Moderator

Report message to a moderator

Re: Trigger for INSERT, UPDATE OR DELETE [message #590225 is a reply to message #590222] Tue, 16 July 2013 00:09 Go to previous message
Michel Cadot
Messages: 59295
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I have read that insert and update could be in one procedure and delete should be in the other. Any comments on that?


Where did you read that?
What was the reason on the one that wrote that?


Regards
Michel
Previous Topic: overlap data need
Next Topic: Between Vs less that & greater than for Date columns
Goto Forum:
  


Current Time: Thu Oct 02 05:18:16 CDT 2014

Total time taken to generate the page: 0.11754 seconds