SQLTeam.com | Weblogs | Forums

Trigger permissions issue?

I'm trying to cascade a status update on an event to a FuncLoc table, then have the FuncLoc table update with the modified date to I can query it.

The FuncLoc table has a trigger on it, to update the modified date and user whenever it is modified:
IF EXISTS (SELECT 0 FROM Deleted)
BEGIN
IF EXISTS (SELECT 0 FROM Inserted)
BEGIN
UPDATE [dbo].[tblFuncLoc]
SET [ModifiedDate] = @dt
,[ModifiedUser] = @login_name
WHERE [tblFuncLoc].[FuncLocID] IN (SELECT [FuncLocID] FROM Inserted);
END
END

and it seems to work OK when I update an entry in SSMS or from my front end.

The event table has a trigger on it to tell the FuncLoc table to retrieve the latest availability from the events, launched as:
IF EXISTS (SELECT 0 FROM Deleted)
BEGIN
IF EXISTS (SELECT 0 FROM Inserted)
BEGIN
EXEC dbo.tblFuncLoc_uspAvail @FuncLocID_New;
END
END

It also has some code in it to update the modified date and user, not shown here, so I know it is running.

tblFuncLoc_uspAvail:
CREATE PROCEDURE [dbo].[tblFuncLoc_uspAvail]
@FuncLocID int,
@Available bit=1 output

with execute as owner

AS
BEGIN
SET NOCOUNT ON;

WITH cte_FuncLocAvail 
AS
  (SELECT FuncLocID, 
		  Available,
		  Row_Number() OVER (PARTITION BY FuncLocID ORDER BY FuncLocID ASC, EventSeqTime DESC)  
		      AS RowNum FROM [dbo].[vwEventSeq])

UPDATE [dbo].[tblFuncLoc]
	SET Available = (SELECT Available FROM cte_FuncLocAvail 
	WHERE cte_FuncLocAvail.FuncLocID = @FuncLocID 
	AND RowNum = 1)
	WHERE [tblFuncLoc].[FuncLocID] = @FuncLocID

END

When I update the event table, I see the tblFuncLoc_uspAvail execute, and the Available field gets updated in the FuncLoc table. However, the FuncLoc modified date does not get updated.

I suspect it is a permissions issue, and I've been playing with the execute as function. I've been able to get the "execute as" function to "compile" in the uspAvail procedure, but it the FuncLoc trigger still doesn't execute. I put some error tracking into the FuncLoc trigger routine to verify this, I'm sure there are better ways.

A topic here suggested I grant "View database state" permissions, still doesn't seem to make a difference.

Unfortunately, my permissions are restricted on the server and database engine, I can only work with this one database, and cannot see the system tables.

As a follow-on, I've granted Delete, Insert, Update, Select and View Definition permissions to all the security groups on my FuncLoc table, and I tried it with using a separate account with the same permissions, no luck yet.

The first place I would look at is whether Nested Triggers is enabled. By default it is set to off.

1 Like

You are indeed correct, nested triggers are disabled. I've put a request into our db team to allow them, hopefully they agree and that fixes the problem.

Just a quick follow-up, the dB team allowed the settings change, and the triggers started working. Thank you!