Delete Triggers - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

Delete Triggers

Author

Topic

JJ
Starting Member

23 Posts

Posted - 2002-03-19 : 17:02:21

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,

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-03-19 : 17:06:40

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>

JJ
Starting Member

23 Posts

Posted - 2002-03-19 : 20:22:45

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

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-19 : 20:24:06

Look at the post just above your reply, that's exactly what it does.

JJ
Starting Member

23 Posts

Posted - 2002-03-20 : 12:36:42

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

Jay99

468 Posts

Posted - 2002-03-20 : 12:46:52

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>

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-20 : 12:49:43

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.

JJ
Starting Member

23 Posts

Posted - 2002-03-20 : 14:25:39

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

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-03-20 : 14:31:05

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...

JJ
Starting Member

23 Posts

Posted - 2002-03-20 : 14:35:35

oh and by the way I am using MSDE 2000 which is pretty much Sql Server 2000.

JJ

JJ
Starting Member

23 Posts

Posted - 2002-03-20 : 15:36:23

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

JJ
Starting Member

23 Posts

Posted - 2002-03-20 : 16:15:17

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources