| Compound Trigger [message #649345] |
Wed, 23 March 2016 03:28  |
pstanand
Messages: 133
Registered: February 2005
Location: Chennai,India
|
Senior Member |
|
|
Hi,
I have the following material table. In which I have a trigger which when updating the material_group,it checks in the same table whether the material_group is available in the set of material_groups('AL','BXU','CM'). If the material_group is available in the set then it delete that record from the table. If the material_group is not available in the given set and it is new then it allows to update. The available trigger is migrated from SQL server and in oracle it is looks like below.
This trigger throws mutating error. Kindly help me to alter the trigger.
Existing trigger that is migrated from SQL Server code:
CREATE OR REPLACE TRIGGER UPD_MAT
BEFORE INSERT OR UPDATE ON material
FOR EACH ROW
BEGIN
IF (:NEW.material_group IS NOT NULL)
THEN
BEGIN
DELETE material
WHERE :NEW.material_num IN (SELECT :NEW.material_num
FROM material
WHERE material_group IN ( 'AL','BXU','CM'));
END;
END IF;
END UPD_MAT;
Table Material Structure
MATERIAL_NUM VARCHAR2(18)NOT NULL
CODE VARCHAR2(20)
MATERIAL_GROUP VARCHAR2(5)
Thanks and Regards
pstanand.
|
|
|
|
| Re: Compound Trigger [message #649347 is a reply to message #649345] |
Wed, 23 March 2016 03:40  |
John Watson
Messages: 8938
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
What you are trying to do is logically impossible. Consider the situation where your INSERT or UPDATE statement affects several rows. In that case this,
DELETE material
WHERE :NEW.material_num IN (SELECT :NEW.material_num
FROM material
WHERE material_group IN ( 'AL','BXU','CM'));
cannot see a consistent version of the table, because it runs per row, not for the whole row set.
I think you probably want to throw out the trigger, and use a MERGE statement rather than INSERTs and UPDATEs.
Generally speaking, any use of triggers to enforce business logic is questionable. Avoid them if you can.
|
|
|
|
| Re: Compound Trigger [message #649349 is a reply to message #649347] |
Wed, 23 March 2016 04:36 |
pstanand
Messages: 133
Registered: February 2005
Location: Chennai,India
|
Senior Member |
|
|
Hi John,
Thanks for your suggestion. In such case can I go with the following merge statement?
MERGE INTO material a
USING material b
ON (a.material_num = b.material_num)
WHEN NOT MATCHED THEN
UPDATE SET a.material_group = b.material_group;
WHEN MATCHED THEN
DELETE FROM material WHERE a.material_num = <material_num>
Kindly correct me if I'm wrong anywhere here.
Thanks
pstanand
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
