Home » SQL & PL/SQL » SQL & PL/SQL » reg:trigger requirement (Oracle, 10g, windows XP)
reg:trigger requirement [message #350483] Thu, 25 September 2008 03:39 Go to next message
jyothsna1612
Messages: 68
Registered: June 2008
Member
Hi
I need to write a trigger with the following specifications... but i'm struck at one requirement on how to implement?

Table name : REF_CGSC_T
Trigger name : G_REF_CGSC_DSDY_FSDO_IUDAR
Trigger Action : Insert, Update, Delete
Trigger Role : Modify DWH
Action Columns : QPP_CNTRY_ID

Rule:
(1) WHEN OLD.QPP_CNTRY_ID IS NOT NULL OR NEW.QPP_CNTRY_ID IS NOT NULL
(2) Treat UPDATE as DELETE (if OLD.QPP_CNTRY_ID IS NOT NULL)
followed by INSERT (if NEW.QPP_CNTRY_ID IS NOT NULL)

Note: whenever any I/U/D operation occurs on REF_CGSC_T corresponding new and old values should be inserted in Destination Table (T_DCCP_D)

The doubt is:
The first rule can be written in The when condition of the trigger...
but how to implement the 2nd Rule...

Could you please give some idea on implementing 2nd rule into code....

Thank you
jyothsna
Re: reg:trigger requirement [message #350520 is a reply to message #350483] Thu, 25 September 2008 05:07 Go to previous message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Rule:
(1) WHEN OLD.QPP_CNTRY_ID IS NOT NULL OR NEW.QPP_CNTRY_ID IS NOT NULL
(2) Treat UPDATE as DELETE (if OLD.QPP_CNTRY_ID IS NOT NULL)
followed by INSERT (if NEW.QPP_CNTRY_ID IS NOT NULL)

Better to do it with a procedure.
If you really want to do it with use "INSTEAD OF" one.

Regards
Michel
Previous Topic: Converting non-partitioned table into partitioned table
Next Topic: What is this technique called????
Goto Forum:
  


Current Time: Fri Dec 09 06:17:26 CST 2016

Total time taken to generate the page: 0.07690 seconds