March 24, 2009 at 10:53 am
I've written my first trigger and I was hoping someone could look over it to reassure me it isn't going to do anything catastrophic. The aim of the trigger is to delete a users records in other tables if their username is ever set to null. The table the trigger is on is updated via replication.
USE [MyDB]
GO
/****** Object: Trigger [dbo].[deleteUsersRolesEtc] Script Date: 03/24/2009 16:52:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[myTriggerName]
ON [dbo].[Users]
FOR UPDATE
AS
IF((SELECT TOP 1 username FROM inserted) IS NULL)
BEGIN
SET NOCOUNT ON;
DELETE FROM user_roles WHERE user_id in (SELECT id FROM inserted WHERE username IS NULL)
DELETE FROM user_permissions WHERE user_id in (SELECT id FROM inserted WHERE username IS NULL)
RETURN
END
March 24, 2009 at 10:59 am
Looks OK. The only thing I'd change is that if statement. If there's an update that sets some usernames to null and others to a non-null value, the IF may not do what you want.
IF EXISTS (SELECT 1 FROM inserted WHERE username IS NULL)
BEGIN
....
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 24, 2009 at 11:06 am
I'd agree with Gail here. You might cause issues with the TOP 1 if you have multiple rows updated
March 24, 2009 at 7:04 pm
only sure way is to test
few months back we set up a logon trigger and tested in QA first. there was a mistake in the code and we locked them out for half a day
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply