Home » SQL & PL/SQL » SQL & PL/SQL » PL-SQL Trigger ..... Help needed
PL-SQL Trigger ..... Help needed [message #214365] Tue, 16 January 2007 00:57 Go to next message
arz_razi
Messages: 2
Registered: January 2007
Junior Member
I have 4 table. 1 is main table where i need to update the account status based on the update made to the other 3 table. Please help me and correct me if i'm wrong based on the code below.

CREATE OR REPLACE TRIGGER "tri_acc_status_upd"
BEFORE

UPDATE
ON "A_DATA","B_DATA","C_DATA"

FOR EACH ROW
WHEN(new.ACCOUNT_STATUS1 != old.ACCOUNT_STATUS1 or
new.ACCOUNT_STATUS2 != old.ACCOUNT_STATUS2 or
new.ACCOUNT_STATUS3 != old.ACCOUNT_STATUS3)

BEGIN
if updating then
begin
UPDATE HERE_TO_UPDATE_DATA discData1 -- alias
SET (ACCOUNT_STATUS1) =
(SELECT ACCOUNT_STATUS1 FROM A_DATA
WHERE ACCOUNT_NO1 = discData1.ACCOUNT_NO1);

UPDATE HERE_TO_UPDATE_DATA discData2 -- alias
SET (ACCOUNT_STATUS2) =
(SELECT ACCOUNT_STATUS2 FROM B_DATA
WHERE ACCOUNT_NO2 = discData2.ACCOUNT_NO2);

UPDATE HERE_TO_UPDATE_DATA discData3 -- alias
SET (ACCOUNT_STATUS3) =
(SELECT ACCOUNT_STATUS3 FROM C_DATA
WHERE ACCOUNT_NO3 = discData3.ACCOUNT_NO3);
end;
end if;
END;


YOUR HELP IS REALLY APPRECIATED.

[Updated on: Tue, 16 January 2007 01:40]

Report message to a moderator

Re: PL-SQL Trigger ..... Help needed [message #214380 is a reply to message #214365] Tue, 16 January 2007 03:02 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
You cannot create one trigger for 3 tables ( provided that those "A_DATA","B_DATA" and "C_DATA" are indeed tables ). You need one trigger per table.

If I understand you correct, you want to update Table_1 based on updates on Table_2, Table_3 or Table_4.

MHE
Re: PL-SQL Trigger ..... Help needed [message #214387 is a reply to message #214365] Tue, 16 January 2007 03:42 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
As Maaher stated, you need three triggers. Eg for the first table something like
CREATE OR REPLACE TRIGGER "tri_acc_status1_upd"
BEFORE UPDATE OF ACCOUNT_STATUS1 ON A_DATA
FOR EACH ROW
BEGIN
UPDATE HERE_TO_UPDATE_DATA
SET ACCOUNT_STATUS1 = :NEW.ACCOUNT_STATUS1
WHERE ACCOUNT_NO1 = :NEW.ACCOUNT_NO1;
END;
/

Suppose ACCOUNT_NO1 is at least unique constraint (if not primary key) in A_DATA.
As these columns are redundant in HERE_TO_UPDATE_DATA, have you thought about not storing them there and get the required values dynamically from A_DATA, B_DATA and C_DATA (eg. by a view)?
Re: PL-SQL Trigger ..... Help needed [message #214748 is a reply to message #214365] Wed, 17 January 2007 21:56 Go to previous messageGo to next message
arz_razi
Messages: 2
Registered: January 2007
Junior Member
thanks for the reply.

UPDATE HERE_TO_UPDATE_DATA
SET ACCOUNT_STATUS1 = :NEW.ACCOUNT_STATUS1
WHERE ACCOUNT_NO1 = :NEW.ACCOUNT_NO1;
END;

Is it the :NEW.ACCOUNT_STATUS1 and :NEW.ACCOUNT_NO1 refer to the field in table A_DATA? Do i need to add select statement for it?
Re: PL-SQL Trigger ..... Help needed [message #214755 is a reply to message #214748] Wed, 17 January 2007 22:48 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Yes, the :NEW structure contains values of the updated row in A_DATA (as the trigger is on A_DATA). If you do not want values from any other A_DATA rows, there is no need to SELECT. Why don't you try (and check) it?
Previous Topic: Polling table for new data
Next Topic: SET COMMANDS
Goto Forum:
  


Current Time: Mon Dec 05 10:40:45 CST 2016

Total time taken to generate the page: 0.06393 seconds