Author |
Topic |
Vack
Aged Yak Warrior
530 Posts |
Posted - 2008-05-13 : 08:33:01
|
Trying to create a trigger on a table that will delete any records in it that do not exist in another table. Inserting into OrderRebateHistory Table. if ordtype, ord_no, and line_seq_no do not exist in oelinhst then delete from OrderRebateHistory or do not insert. Right now my code will insert the record. Then when another record gets inserted the previous record is deleted. CREATE TRIGGER [DeletefromOrderRebateHistory] ON [dbo].[OrderRebateHistory] for INSERTASDELETE OrderRebateHistoryFROM OrderRebateHistory inner join oelinhst_sql on oelinhst_sql.ord_type = OrderRebateHistory.ord_type andoelinhst_sql.ord_no = OrderRebateHistory.ord_no and oelinhst_sql.line_seq_no = OrderRebateHistory.line_seq_nowhere oelinhst_sql.ord_type <>OrderRebateHistory.ord_type and oelinhst_sql.ord_no <> OrderRebateHistory.ord_noand oelinhst_sql.line_seq_no <> OrderRebateHistory.line_seq_no |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-05-13 : 11:00:26
|
There is no need for insert to happen you can have a INSTEAD OF INSERT trigger for this:-CREATE TRIGGER YourTrigger ON OrderRebateHistoryINSTEAD OF INSERTASBEGINIF NOT EXISTS(SELECT * FROM oelinhst oINNER JOIN INSERTED iON i.ordtype = o.ordtypeAND i.ord_no=o.ord_noand i.line_seq_no=o.line_seq_no)BEGININSERT INTO OrderRebateHistorySELECT * FROM INSERTEDENDEND This will check if record going to inserted is already in oelinhst and wont insert if one exists |
|
|
Vack
Aged Yak Warrior
530 Posts |
Posted - 2008-05-13 : 11:58:08
|
Still have the same problem. It is writing the record to the OrderRebateHistory and removing the record that was in the table. Example: I delete order 89 from the orderline table which writes a record to the OrderRebateHistory.Then I enter another order 90 and delete it. After deleting it, order 89 is removed from the OrderRebateHistory table and order 90 gets written. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-05-13 : 12:07:21
|
quote: Originally posted by Vack Still have the same problem. It is writing the record to the OrderRebateHistory and removing the record that was in the table. Example: I delete order 89 from the orderline table which writes a record to the OrderRebateHistory.Then I enter another order 90 and delete it. After deleting it, order 89 is removed from the OrderRebateHistory table and order 90 gets written.
Ok. SO this table is populated by trigger. So i guess its not allowing the other trigger to be fired. Are you sure you have nested trigger option set to true for server?http://msdn.microsoft.com/en-us/library/ms178101.aspx |
|
|
Vack
Aged Yak Warrior
530 Posts |
Posted - 2008-05-13 : 12:13:07
|
YES IT IS SET |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-05-13 : 12:21:10
|
quote: Originally posted by Vack YES IT IS SET
Whats the trigger on orderline doing? |
|
|
Vack
Aged Yak Warrior
530 Posts |
Posted - 2008-05-13 : 12:27:04
|
The trigger is actually on the orderrebate table. When a record is deleted from it I want it to go to the history. CREATE TRIGGER [UPDATEOrderRebateHistory] ON [dbo].[OrderRebate] for deleteasupdate OrderRebateHistoryset ord_type = deleted.ord_type,ord_no = deleted.ord_no,line_seq_no = deleted.line_seq_no,status = deleted.status,item_no = deleted.item_no,price = deleted.price,rebate_amt = deleted.rebate_amt,ext_rebate = deleted.ext_rebate,rebate_pct = deleted.rebate_pct,qty_ordered = deleted.qty_ordered,qty_to_ship = deleted.qty_to_ship,cus_no = deleted.cus_no,AccountTypeCode = deleted.AccountTypeCode,cd_tp = deleted.cd_tp,A4ID = deleted.A4IDFROM deleted |
|
|
Vack
Aged Yak Warrior
530 Posts |
Posted - 2008-05-13 : 12:49:15
|
One other thing. The trigger above get fired when this one does which is on the oeordlin table.CREATE TRIGGER [DeletefromOrderRebate] ON [dbo].[oeordlin_sql] for DELETEASDELETE OrderRebateFROM OrderRebate inner join deleted on deleted.ord_type = OrderRebate.ord_type anddeleted.ord_no = OrderRebate.ord_no and deleted.line_seq_no = OrderRebate.line_seq_nowhere deleted.ord_type = OrderRebate.ord_type and deleted.ord_no = OrderRebate.ord_noand deleted.line_seq_no = OrderRebate.line_seq_no |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-05-13 : 12:54:12
|
quote: Originally posted by Vack The trigger is actually on the orderrebate table. When a record is deleted from it I want it to go to the history. CREATE TRIGGER [UPDATEOrderRebateHistory] ON [dbo].[OrderRebate] for deleteasupdate OrderRebateHistoryset ord_type = deleted.ord_type,ord_no = deleted.ord_no,line_seq_no = deleted.line_seq_no,status = deleted.status,item_no = deleted.item_no,price = deleted.price,rebate_amt = deleted.rebate_amt,ext_rebate = deleted.ext_rebate,rebate_pct = deleted.rebate_pct,qty_ordered = deleted.qty_ordered,qty_to_ship = deleted.qty_to_ship,cus_no = deleted.cus_no,AccountTypeCode = deleted.AccountTypeCode,cd_tp = deleted.cd_tp,A4ID = deleted.A4IDFROM deleted
This wont fire the instead of insert trigger as its doing an update. So i guess you have to change other trigger to instead of update. |
|
|
Vack
Aged Yak Warrior
530 Posts |
Posted - 2008-05-13 : 12:59:09
|
I'm not following. Which trigger needs changed? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-05-13 : 13:24:31
|
Try changing it like thisCREATE TRIGGER YourTrigger ON OrderRebateHistoryINSTEAD OF INSERT UPDATEASBEGINIF NOT EXISTS(SELECT * FROM oelinhst oINNER JOIN INSERTED iON i.ordtype = o.ordtypeAND i.ord_no=o.ord_noand i.line_seq_no=o.line_seq_no)BEGININSERT INTO OrderRebateHistorySELECT * FROM INSERTEDENDEND |
|
|
Vack
Aged Yak Warrior
530 Posts |
Posted - 2008-05-13 : 13:26:34
|
I believe I've solved the problem with the following. Done a little testing and it seems to work good. moved the trigger to the orderrebate table instead of the history table. CREATE TRIGGER [UPDATEOrderRebateHistory] ON [dbo].[OrderRebate] for deleteasupdate OrderRebateHistoryset ord_type = deleted.ord_type,ord_no = deleted.ord_no,line_seq_no = deleted.line_seq_no,status = deleted.status,item_no = deleted.item_no,price = deleted.price,rebate_amt = deleted.rebate_amt,ext_rebate = deleted.ext_rebate,rebate_pct = deleted.rebate_pct,qty_ordered = deleted.qty_ordered,qty_to_ship = deleted.qty_to_ship,cus_no = deleted.cus_no,AccountTypeCode = deleted.AccountTypeCode,cd_tp = deleted.cd_tp,A4ID = deleted.A4IDFROM deleted join oelinhst_sql on deleted.ord_type = oelinhst_sql.ord_type anddeleted.ord_no = oelinhst_sql.ord_no and deleted.line_seq_no = oelinhst_sql.line_seq_nowhere deleted.ord_type = oelinhst_sql.ord_type anddeleted.ord_no = oelinhst_sql.ord_no and deleted.line_seq_no = oelinhst_sql.line_seq_no |
|
|
|