August 20, 2004 at 2:29 pm
I created a trigger that prevents users from deleting from the project_contacts table if they are not a team member of that specific project (silbling table to project contacts). However, If a user attempts to delete more than one row at a time, they get an error b/c the code is attempting to set a variable to a SELECT statement that is returning multiple values. I am experienced in creating triggers in Oracle, where there is a "FOR EACH ROW" option (which executes the trigger seperately for each row deleted/updated/inserted). Is there a similar command in SQL server? If not, how would I modify the trigger to work as desired? (trigger text below)
CREATE TRIGGER trgProjContactsUD ON dbo.tblProjContacts
FOR UPDATE, DELETE
AS
DECLARE
@proj int,
@member int
SET @proj = (SELECT Project_FK FROM Deleted)
SET @member = (SELECT COUNT(*) FROM dbo.tblProjTeam WHERE Project_FK = @proj AND Employee_FK = dbo.fncEmpID(USER))
IF (IS_MEMBER('MGR') = 0 AND IS_MEMBER('db_owner') = 0 AND @member = 0)
BEGIN
RAISERROR ('You are not a project team member', 16, 1)
ROLLBACK TRANSACTION
END
August 20, 2004 at 3:52 pm
SQL doesnt have a similar command. You either have to write a cursor that loops through the logical inserted or deleted tables and processes each row, or you have to convert to use set based syntax so you can do all at once. Set based is preferred, not always possible.
The alternative would be to not let users delete more than one row at a time. You could rollback if more than one record in the deleted table to be sure.
August 20, 2004 at 8:52 pm
Piggy-backing on what Andy has said, if you're on SQL Server 2000, take a look at INSTEAD OF TRIGGERS. INSTEAD OF TRIGGERS fire before the data is touched, meaning you wouldn't have to roll back (SQL Server 7 you have no option, the only trigger available fires after and as a result, you have to roll back). Get the number of records in deleted. If it's greater than 1, the INSTEAD OF trigger overrides the delete and you just drop out of the trigger. Otherwise, re-execute the DELETE using the value in the deleted table and you're good.
K. Brian Kelley
@kbriankelley
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply