Home » SQL & PL/SQL » SQL & PL/SQL » Help in Oracle trigger
Help in Oracle trigger [message #37069] Tue, 15 January 2002 06:00 Go to next message
Bhagwan Singh
Messages: 23
Registered: December 2001
Junior Member
Hi all,

Iam writing an audit trigger for update on a table and need to know if it possible to write 'conditional statements' in the WHEN CLAUSE of trigger.For example.

Example:

CREATE OR REPLACE TRIGGER Tr_Upd_Marketing
AFTER
UPDATE ON tb_marketing
FOR EACH ROW
WHEN (OLD.source_of_wealth_code <> NEW.source_of_wealth_code) OR (OLD.liquid_assets <> NEW.liquid_assets) OR (OLD.non_liquid_assets <> NEW.non_liquid_assets)
BEGIN
IF UPDATING('source_of_wealth_code') THEN
Generic_Insert('tb_marketing_audit', 7, :OLD.Client_code, 'tb_marketing','source_of_wealth_code', SYSDATE, :OLD.source_of_wealth_code, :NEW.source_of_wealth_code, USER, NULL);
END IF;
IF UPDATING('liquid_assets') THEN
Generic_Insert('tb_marketing_audit', 7, :OLD.Client_code, 'tb_marketing','liquid_assets', SYSDATE, :OLD.liquid_assets, :NEW.liquid_assets, USER, NULL);
END IF;
IF UPDATING('non_liquid_assets') THEN
Generic_Insert('tb_marketing_audit', 7, :OLD.Client_code, 'tb_marketing','non_liquid_assets', SYSDATE, :OLD.non_liquid_assets, :NEW.non_liquid_assets, USER, NULL);
END IF;

END Tr_Upd_Marketing;

Iam calling a procedure which updates above table.But it happen sometimes that there are certain fields which are not updated everytime.Therfore I need to fire my trigger only for specific fields and not for all the fields.

Execution of above trigger fills my audit table with those fields also where the old and new data are same.
how can I avoid this problem?Do I have to write

"IF UPDATING('non_liquid_assets') AND (:OLD.non_liquid_assets <> :NEW.non_liquid_assets) THEN"

FOR EACH AND EVERY STATMENT?
Please help
bhagwan
Re: Help in Oracle trigger [message #37070 is a reply to message #37069] Tue, 15 January 2002 06:34 Go to previous message
pratap kumar tripathy
Messages: 660
Registered: January 2002
Senior Member
yes u can write when condition. try this( i have changed a bit)let me know if still prolem persist.

CREATE OR REPLACE TRIGGER Tr_Upd_Marketing
AFTER
UPDATE ON tb_marketing
FOR EACH ROW
WHEN (OLD.source_of_wealth_code != NEW.source_of_wealth_code) OR (OLD.liquid_assets != NEW.liquid_assets) OR (OLD.non_liquid_assets != NEW.non_liquid_assets)
BEGIN
IF :OLD.source_of_wealth_code != :NEW.source_of_wealth_code THEN
Generic_Insert('tb_marketing_audit', 7, :OLD.Client_code, 'tb_marketing','source_of_wealth_code', SYSDATE, :OLD.source_of_wealth_code, :NEW.source_of_wealth_code, USER, NULL);
END IF;
IF :OLD.liquid_assets != :NEW.liquid_assets THEN
Generic_Insert('tb_marketing_audit', 7, :OLD.Client_code, 'tb_marketing','liquid_assets', SYSDATE, :OLD.liquid_assets, :NEW.liquid_assets, USER, NULL);
END IF;
IF :OLD.non_liquid_assets != :NEW.non_liquid_assets THEN
Generic_Insert('tb_marketing_audit', 7, :OLD.Client_code, 'tb_marketing','non_liquid_assets', SYSDATE, :OLD.non_liquid_assets, :NEW.non_liquid_assets, USER, NULL);
END IF;

END Tr_Upd_Marketing;
Previous Topic: detailed sysdate
Next Topic: SQL-Statement too long?
Goto Forum:
  


Current Time: Tue Jul 14 10:15:05 CDT 2020