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
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Trigger delete if no records in another table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Vack
Constraint Violating Yak Guru

USA
496 Posts

Posted - 05/13/2008 :  08:50:57  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

harsh_athalye
Flowing Fount of Yak Knowledge

India
5509 Posts

Posted - 05/13/2008 :  08:55:39  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
Duplicate: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=102741

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
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.06 seconds. Powered By: Snitz Forums 2000