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.