Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SQL Trigger Delete

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 INSERT
AS

DELETE OrderRebateHistory
FROM OrderRebateHistory inner join oelinhst_sql on oelinhst_sql.ord_type = OrderRebateHistory.ord_type and
oelinhst_sql.ord_no = OrderRebateHistory.ord_no and oelinhst_sql.line_seq_no = OrderRebateHistory.line_seq_no
where oelinhst_sql.ord_type <>OrderRebateHistory.ord_type and oelinhst_sql.ord_no <> OrderRebateHistory.ord_no
and 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 OrderRebateHistory
INSTEAD OF INSERT
AS
BEGIN
IF NOT EXISTS
(SELECT * FROM oelinhst o
INNER JOIN INSERTED i
ON i.ordtype = o.ordtype
AND i.ord_no=o.ord_no
and i.line_seq_no=o.line_seq_no)
BEGIN
INSERT INTO OrderRebateHistory
SELECT * FROM INSERTED
END
END


This will check if record going to inserted is already in oelinhst and wont insert if one exists
Go to Top of Page

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.
Go to Top of Page

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
Go to Top of Page

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-05-13 : 12:13:07
YES IT IS SET
Go to Top of Page

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?
Go to Top of Page

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 delete
as

update OrderRebateHistory
set 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.A4ID
FROM deleted
Go to Top of Page

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 DELETE
AS

DELETE OrderRebate
FROM OrderRebate inner join deleted on deleted.ord_type = OrderRebate.ord_type and
deleted.ord_no = OrderRebate.ord_no and deleted.line_seq_no = OrderRebate.line_seq_no
where deleted.ord_type = OrderRebate.ord_type and deleted.ord_no = OrderRebate.ord_no
and deleted.line_seq_no = OrderRebate.line_seq_no
Go to Top of Page

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 delete
as

update OrderRebateHistory
set 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.A4ID
FROM 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.
Go to Top of Page

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-05-13 : 12:59:09
I'm not following. Which trigger needs changed?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-13 : 13:24:31
Try changing it like this

CREATE TRIGGER YourTrigger ON OrderRebateHistory
INSTEAD OF INSERT UPDATE
AS
BEGIN
IF NOT EXISTS
(SELECT * FROM oelinhst o
INNER JOIN INSERTED i
ON i.ordtype = o.ordtype
AND i.ord_no=o.ord_no
and i.line_seq_no=o.line_seq_no)
BEGIN
INSERT INTO OrderRebateHistory
SELECT * FROM INSERTED
END
END
Go to Top of Page

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 delete
as

update OrderRebateHistory
set 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.A4ID
FROM deleted join oelinhst_sql on deleted.ord_type = oelinhst_sql.ord_type and
deleted.ord_no = oelinhst_sql.ord_no and deleted.line_seq_no = oelinhst_sql.line_seq_no
where deleted.ord_type = oelinhst_sql.ord_type and
deleted.ord_no = oelinhst_sql.ord_no and deleted.line_seq_no = oelinhst_sql.line_seq_no
Go to Top of Page
   

- Advertisement -