Home » SQL & PL/SQL » SQL & PL/SQL » Help in Oracle trigger
| Help in Oracle trigger [message #37069] |
Tue, 15 January 2002 06:00  |
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  |
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;
|
|
|
|
Goto Forum:
Current Time: Sat Jun 03 10:09:03 CDT 2023
|
] 
Search
Help
Members
Register
Login
Home
