Home » SQL & PL/SQL » SQL & PL/SQL » Trigger Help
Trigger Help [message #240680] Fri, 25 May 2007 08:51 Go to next message
KenJ
Messages: 69
Registered: July 2006
Location: London
Member
Hi,

I need some help with a trigger please. My PL/SQL knowledge is ancient.

I am using Oracle Enterprise Edition 10.2.0.3.0. running on Solaris 10. Single instance.

I require an after insert or update trigger to fire and to write the insert or update to a copy table over a db link. The db link is not an issue. Can you help with writing the trigger please?

Thanks in advance,

Ken.
Re: Trigger Help [message #240682 is a reply to message #240680] Fri, 25 May 2007 09:01 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
create or replace trigger tx after insert on x 
for each row 
begin 
   insert into remote_tab@dblink (col1, col2,col3,...)
   values (:new.col1, :new.col2, :new.col3,...
end;
Re: Trigger Help [message #240684 is a reply to message #240680] Fri, 25 May 2007 09:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL Reference, CREATE TRIGGER contains examples.

Regards
Michel
Re: Trigger Help [message #240686 is a reply to message #240680] Fri, 25 May 2007 09:10 Go to previous messageGo to next message
KenJ
Messages: 69
Registered: July 2006
Location: London
Member
Thank you. I had the insert trigger worked out as:-

CREATE OR REPLACE TRIGGER <TRIGGERNAME>
AFTER INSERT ON <TABLENAME>
FOR EACH ROW
INSERT INTO <TABLENAME>@<DBLINK> VALUES (:NEW.COL1,:NEW.COL2,:NEW.COL3........................);
COMMIT;
END;

Can you give some pointers n the update trigger please?
Re: Trigger Help [message #240687 is a reply to message #240680] Fri, 25 May 2007 09:10 Go to previous messageGo to next message
KenJ
Messages: 69
Registered: July 2006
Location: London
Member
Hi Michel,
I already have that page open. Thank you.
Ken
Re: Trigger Help [message #240688 is a reply to message #240680] Fri, 25 May 2007 09:13 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
Your insert trigger is wrong. You can't do a commit.
Re: Trigger Help [message #240689 is a reply to message #240680] Fri, 25 May 2007 09:17 Go to previous messageGo to next message
KenJ
Messages: 69
Registered: July 2006
Location: London
Member
Thanks. I have taken it out.
Re: Trigger Help [message #240691 is a reply to message #240680] Fri, 25 May 2007 09:21 Go to previous messageGo to next message
KenJ
Messages: 69
Registered: July 2006
Location: London
Member
CREATE OR REPLACE TRIGGER <TRIGGERNAME>
AFTER UPDATE ON <TABLENAME>
FOR EACH ROW
IF :NEW.COL1 <> :OLD.COL1 THEN <TABLENAME>@<DBLINK> SET COL1 = :NEW.COL1;
COL2................
COL3................
COL4................
END;

Is this ok?
Re: Trigger Help [message #240692 is a reply to message #240680] Fri, 25 May 2007 09:23 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
I think you would need an update statement somewhere.


create or replace trigger tx after update on x 
for each row 
begin 
   update remote_tab@dblink
   set col1 = :new.col1,
       col2 = :new.col2,
       ...
   where keycol1 = :old.keycol1
     and keycol2 = :old.keycol2
     and ....;
end;
Re: Trigger Help [message #240693 is a reply to message #240691] Fri, 25 May 2007 09:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
IF :NEW.COL1 <> :OLD.COL1 THEN <TABLENAME>@<DBLINK> SET COL1 = :NEW.COL1;


What does this mean?

Regards
Michel
Re: Trigger Help [message #240694 is a reply to message #240691] Fri, 25 May 2007 09:25 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
In some language other than Pl/Sql, it might work.

You need something like:
CREATE OR REPLACE TRIGGER test_trg
AFTER UPDATE ON table FOR EACH ROW AS
BEGIN
  update table@db_link
  set    col_1 = :new.col_1
        ,col_2 = :new.col_2
...
  where  primary_key_col = :old.primary_key_col

END
Re: Trigger Help [message #240695 is a reply to message #240692] Fri, 25 May 2007 09:26 Go to previous messageGo to next message
KenJ
Messages: 69
Registered: July 2006
Location: London
Member
CREATE OR REPLACE TRIGGER <TRIGGERNAME>
AFTER UPDATE ON <TABLENAME>
FOR EACH ROW
IF :NEW.COL1 <> :OLD.COL1 THEN UPDATE <TABLENAME>@<DBLINK> SET COL1 = :NEW.COL1 WHERE..........;
COL2................
COL3................
COL4................
END;

I had missed out the UPDATE in my last post. Would the above work?
THanks,
Ken.
Re: Trigger Help [message #240698 is a reply to message #240680] Fri, 25 May 2007 09:33 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
Are you going to repeat this for each column so that, if 5 columns change, you get 5 different update statements ? You might be better using an OR condition and a single update.

You also need to consider NULL values. The condition:

IF :NEW.COL1 <> :OLD.COL1

will return NULL if either :NEW.col1 is NULL or :OLD.col1 is NULL. NULL is not the same as TRUE, so the branch will not be exercised. So if a value is being set to NULL or changed from NULL, your update will not be replicated.

To avoid this, you could use NVL:

IF NVL(:NEW.COL1, 'XX') <> nvl(:OLD.COL1, 'XX') THEN

You would have to change 'XX' to something more suitable if col1 is a number or a date.

Re: Trigger Help [message #240702 is a reply to message #240680] Fri, 25 May 2007 09:48 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
It goes without saying that the 'XX' value would have to be something that can't actually occur in the database. If you can't be confident of finding such a value, you could do it the long way with:

IF (:NEW.COL1 AND :NEW.COL2 IS NOT NULL) OR
   (:NEW.COL1 IS NOT NULL AND :NEW.COL2 IS NULL) OR
   (:NEW.COL1 <> :OLD.COL1) THEN
...

[Updated on: Fri, 25 May 2007 09:49]

Report message to a moderator

Re: Trigger Help [message #240715 is a reply to message #240680] Fri, 25 May 2007 10:04 Go to previous messageGo to next message
KenJ
Messages: 69
Registered: July 2006
Location: London
Member
Thanks Cthulhu
Re: Trigger Help [message #240718 is a reply to message #240715] Fri, 25 May 2007 10:08 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
To be honest, I think you've be better off createing a set of Fast Refresh On Commit Materialized views at the far end of the DB link.
These would automatically replicate any committed transactions over the Db link for you, and you'd have a lot problems.
Previous Topic: I'm ignorant and stubborn - Answers my queries.
Next Topic: Analyze table
Goto Forum:
  


Current Time: Sun Dec 04 00:38:32 CST 2016

Total time taken to generate the page: 0.18253 seconds