SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SQL Trigger Delete
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Vack
Constraint Violating Yak Guru

USA
497 Posts

Posted - 05/13/2008 :  08:33:01  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 05/13/2008 :  11:00:26  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

USA
497 Posts

Posted - 05/13/2008 :  11:58:08  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 05/13/2008 :  12:07:21  Show Profile  Reply with Quote
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

Edited by - visakh16 on 05/13/2008 12:07:44
Go to Top of Page

Vack
Constraint Violating Yak Guru

USA
497 Posts

Posted - 05/13/2008 :  12:13:07  Show Profile  Reply with Quote
YES IT IS SET
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 05/13/2008 :  12:21:10  Show Profile  Reply with Quote
quote:
Originally posted by Vack

YES IT IS SET


Whats the trigger on orderline doing?
Go to Top of Page

Vack
Constraint Violating Yak Guru

USA
497 Posts

Posted - 05/13/2008 :  12:27:04  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

USA
497 Posts

Posted - 05/13/2008 :  12:49:15  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 05/13/2008 :  12:54:12  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

USA
497 Posts

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

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 05/13/2008 :  13:24:31  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

USA
497 Posts

Posted - 05/13/2008 :  13:26:34  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000