Need help with trigger

[sankar6254]

Hi,

I need to write a trigger to satisfy the following condition:

TableA:

Name Type
==== ====

event_id int,
event_class varchar2(255));


TableB:

Name Type
==== ====

event_id int;
attr_name varchar2(255);
attr_value varchar2(255);

TableC:

Name Type
==== ====

event_id int;
Cname varchar2(255);
Cvalue varchar2(255);

TableD:

Name Type
==== ====

event_id int;
Dname varchar2(255);
Dvalue varchar2(255);


For any insert into TableB, if its event_id matches the event_id in TableA
check the event_class in TableA
if it happens to be 'Company'
then insert the event_id, attr_name and a flag 'C' into TableC
else if it happens to be 'Address'
then insert the event_id, attr_name and a flag 'A' into TableD.

Then compare the event_id from TableB and TableC.
If they match, update Cname of TableC with attr_value of TableB.

Then compare the event_id from TableB and TableD.
If they match, update Dname of TableC with attr_value of TableD.

Any pointers on how to write this would be greatly appreciated.

Thanks,
Sankar.

sankar.mandalika@sun.com
sankar6254@yahoo.com

[slimdave]

Comment: Is there an excellent reason forcoding this in a trigger? Complex business logic like this is generally best left to whatever procedure is doing the inserting.