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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Delete Triggers
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

JJ
Starting Member

23 Posts

Posted - 03/19/2002 :  17:02:21  Show Profile  Reply with Quote
Hi Guys,

I am trying to create a trigger that checks for records in other tables and deletes them first then deletes record where trigger is a part of. Is their an example out there that does this. I can create myself if someone could tell me what statements to look at to use.

Ex.
Create Trigger trDelClientRec ON table1
FOR DELETE
AS
Select ClientID From table2 WHERE ClientID = Table1.ClientID
If Exists then
Delete from table2 Where ClientID = table1.ClientID

Now how would I code the If part of this trigger and how do I use parameters
in the trigger to look for a certain record?

Thanks In Advance,

JJ


setbasedisthetruepath
Used SQL Salesman

USA
992 Posts

Posted - 03/19/2002 :  17:06:40  Show Profile  Reply with Quote
quote:
how do I use parameters
in the trigger to look for a certain record?


think set based ...

Create Trigger trDelClientRec ON table1
FOR DELETE
AS
delete table2
from table2 t2
inner join deleted d on t2.clientid = d.clientid

setBasedIsTheTruepath
<O>
Go to Top of Page

JJ
Starting Member

23 Posts

Posted - 03/19/2002 :  20:22:45  Show Profile  Reply with Quote
What I am trying to do with the trigger is a cascading delete across tables based on telling the delete which record I want removed.

I should probably use a stored procedure for this unless someone can tell me how a cascading delete can be done with a value that I supply?

Thanks,

JJ

Go to Top of Page

robvolk
Most Valuable Yak

USA
15732 Posts

Posted - 03/19/2002 :  20:24:06  Show Profile  Visit robvolk's Homepage  Reply with Quote
Look at the post just above your reply, that's exactly what it does.

Go to Top of Page

JJ
Starting Member

23 Posts

Posted - 03/20/2002 :  12:36:42  Show Profile  Reply with Quote
I placed the trigger in as suggested. I preformed a delete on the clients table and came up with this error:

Server: Msg 547, Level 16, State 1, Line 1
DELETE statement conflicted with TABLE REFERENCE constraint 'FK_DAUse_Clients'. The conflict occurred in database 'SIIP', table 'DAUseHistory'.
The statement has been terminated.

I have a foreign key on the DAUseHistory table that references the Primary key in the Clients table. Here is the trigger I put in:

CREATE TRIGGER trDelClientRec ON [dbo].[Clients]
FOR DELETE
AS
DELETE DAUseHistory FROM DaUseHistory d1
INNER JOIN deleted d ON d1.clientID = d.ClientID AND d1.SIIP_NUMBER = d.SIIP_NUMBER


What am I doing wrong here ??? I am guessing that I need to delete first from the DAUSeHistory table even though the primary key is in the Clients table.

JJ


Go to Top of Page

Jay99
Constraint Violating Yak Guru

USA
468 Posts

Posted - 03/20/2002 :  12:46:52  Show Profile  Reply with Quote
SQL Server, by default uses 'after-triggers', meaning the dml in the trigger is fired after the delete has taken place. You trigger is never firing, because the delete violates your foreign key constraint and is causing an error that rollsback your transaction.

You have a couple options...
1) disable the constraint before the delete statement
2) use sql 2000 and implement an 'INSTEAD OF' trigger to delete the dependecy first, then the client
3) bag the trigger idea and manage this some other way

Jay
<O>
Go to Top of Page

robvolk
Most Valuable Yak

USA
15732 Posts

Posted - 03/20/2002 :  12:49:43  Show Profile  Visit robvolk's Homepage  Reply with Quote
The FOREIGN KEY declaration will not allow you to delete rows from the parent table if they relate to rows in a child table. You have to remove the foreign key declaration in order for the trigger to work properly.

Do you have SQL 2000? If you do, it supports ON DELETE CASCADE for foreign keys.

If you don't have it, and you MUST have the cascade delete, then you need to write INSERT and UPDATE triggers to maintain the foreign key as well as the cascade action.

I know that at least one of the gurus (Arnold, nr, byrmol, or maybe another) suggest that you have a column like "active" or "inactive" or "deleted" in your parent table, and this has a flag that indicates the row is deleted. That way, you can enforce your foreign keys without having to delete rows.

If you need to physically remove rows, you can supplement this design with a scheduled job that disables the foreign keys, deletes all the rows in all the affected tables, then re-establishes the foreign keys. This would run with your regular maintenance schedule, say after you complete your backups.

Go to Top of Page

JJ
Starting Member

23 Posts

Posted - 03/20/2002 :  14:25:39  Show Profile  Reply with Quote
ok the ON DELETE CASCADE sounds like an option so rebuild the key on clients table to include the ON DELETE CASCADE option is what you are saying?


JJ


Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

USA
3246 Posts

Posted - 03/20/2002 :  14:31:05  Show Profile  Visit AjarnMark's Homepage  Reply with Quote
Just to be sure we're talking the same thing here, the ON DELETE CASCADE option is only available in SQL 2000, it does not exist in SQL 7 or earlier.

If we're okay there, then the cascade option is used in the Foreign Key CONSTRAINT declaration.

------------------------
GENERAL-ly speaking...
Go to Top of Page

JJ
Starting Member

23 Posts

Posted - 03/20/2002 :  14:35:35  Show Profile  Reply with Quote
oh and by the way I am using MSDE 2000 which is pretty much Sql Server 2000.

JJ

Go to Top of Page

JJ
Starting Member

23 Posts

Posted - 03/20/2002 :  15:36:23  Show Profile  Reply with Quote
Ok I added a ON CASCADING DELETE for my foreign Keys. Now I am assuming that I don't need the trigger on Clients Table anymore. So if I place a Delete on the clients table it will go down the other tables and delete the particular record. correct?


Thanks,

JJ

Go to Top of Page

JJ
Starting Member

23 Posts

Posted - 03/20/2002 :  16:15:17  Show Profile  Reply with Quote
Yes it WORKS !!!! I just added the ON CASCADING DELETES to the contraints on the foreign tables.

I am using MSDE 2000 or version 2 as a database. It seems to have all the functionality that SQL Server 2000 has. I am using it with access 2000 as a front end to MSDE 2000. Pretty neat stuff!

Thanks all that replyed,

JJ


Go to Top of Page
  Previous Topic Topic Next 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.05 seconds. Powered By: Snitz Forums 2000