Simulating cascade deletes in SQL Server 7.0 using TRIGGERS

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)

Simulating cascade deletes in SQL Server 7.0 using TRIGGERS

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-02-26 : 09:11:48

micheal writes "The following is a stored procedure and a trigger that i am using in a college project, i think syntactically that the trigger is correct. but it just aint been called when the spDeleteUsers stored procedure is called.

Please note: all tables that are deleted from are only tied to tblUsers so i don't think that the order of deletion matters
as they have no fields that are Foreign keys in other tables:
anyways i'm deleting them in the order that the relationships are created also no other triggers exist therefore not a recursive trigger and also the trigger has been enabled

ANY help would be much appreciated
cheers
micheal

CREATE TRIGGER trCascadeDeleteUsers
ON tblUsers
FOR DELETE
AS
DECLARE @UserId int
SELECT @UserId = User_Id
FROM Deleted
DELETE from tblTimesheets
WHERE tblTimesheets.User_Id = @UserId
DELETE from tblTimesheetsItem
WHERE tblTimesheetsItems.User_Id = @UserId
DELETE from tblUserAssignedTasks
WHERE tblUserAssignedTasks.User_Id = @UserId
DELETE from tblGroups
WHERE tblGroups.User_Id = @UserId

CREATE PROCEDURE spDeleteUsers
@UserId int
AS
DELETE
FROM tblUsers
WHERE UserId = @UserId

Cheers Micheal"

Nazim
A custom title

1408 Posts

Posted - 2002-02-26 : 10:20:16

Have a look at the profiler for the value of @UserID.

i would suggest you to change your delete statements on this lines. as your existing assingment of user_id to @user_id might work only for a single record deletion( Am not sure about it you should check it).

DELETE from tblTimesheets
WHERE tblTimesheets.User_Id = @UserId

to

DELETE from tblTimesheets
from tblTimeSheets t inner join Deleted d
on t.user_id=d.userid

--------------------------------------------------------------

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources