I have table tblA
Which has a trigger defined on it, if any rows are updated it writes the changes to an audit table.
Now if on SQL Server I right click and edit a row directly, I see changes go to the audit table.
If I call a stored procedure to do an update, I do see tblA
updated but the changed values do not go into the audit table. It seems like the trigger does not get fired at all.
What difference is there between directly editing a table or updating through a stored procedure in term of the trigger being fired.
Trigger
USE [dbSuppHousing]
GO
/****** Object: Trigger [dbo].[tblSurvey_trigger] Script Date: 9/22/2015 2:32:51 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/* =============================================
Create date: 08/27/15
Description: Trigger to Add audit records to audtSurvey
// =============================================*/
ALTER TRIGGER [dbo].[tblSurvey_trigger] on [dbo].[tblSurvey]
FOR UPDATE,DELETE
AS
SET NOCOUNT ON
Declare @FieldName varchar(100)
, @Deleted varchar(4000)
, @Inserted varchar(4000)
, @SurveyID numeric
, @LoginName varchar(100)
Declare @currentDate datetime = null
SET @currentDate = getdate()
Set @Deleted = ''
Set @Inserted = ''
Select * into #Deleted From Deleted
Select * into #Inserted From Inserted
Create Table #DT ([NameValue] varchar(1000))
Create Table #IT ([NameValue] varchar(1000))
Begin Transaction
Declare auSurveyCur cursor for Select Survey_ID from #Deleted Order By Survey_ID
open auSurveyCur
fetch next from auSurveyCur into @SurveyID
while @@fetch_status = 0
Begin
Declare auU cursor
for Select [name] from syscolumns where id = object_id('dbo.tblSurvey')
open auU
fetch next from auU into @FieldName
while @@fetch_status = 0
Begin
Insert into #DT execute ('Select ' + @FieldName + ' From #Deleted Where Survey_ID = ' + @SurveyID)
Insert into #IT execute ('Select ' + @FieldName + ' From #Inserted Where Survey_ID = ' + @SurveyID)
Set @Deleted = (Select isnull(NameValue,'--') From #DT)
Set @Inserted = (Select isnull(NameValue,'--') From #IT)
If (@Deleted <> @Inserted)
Begin
SELECT @LoginName=Login_Name
From Inserted Where Survey_ID=@SurveyID
if @Deleted = '--' begin set @Deleted = null end
if @Inserted = '--' begin set @Inserted = null end
--@ForWhat=1 means info is for tbSurvey
--In future there may be more tables for auditing and we use same
--Stored procedure to insert audit. Each table has its own audit table.
Execute dbo.InsertauInfo @Surv_ID=@SurveyID
, @auSurveyFieldName=@FieldName
, @auSurveyChangedFrom=@Deleted
, @auSurveyChangedTo=@Inserted
, @auSurveyUpdatedBy=@LoginName
, @auSurveyUpdateDate=@currentDate
, @ForWhat=1
End
Delete From #DT
Delete From #IT
fetch next from auU into @FieldName
End
close auU
deallocate auU
fetch next from auSurveyCur into @SurveyID
END
close auSurveyCur
deallocate auSurveyCur
Commit Transaction
Code for InsertauInfo
ALTER PROCEDURE [dbo].[InsertauInfo]
@Surv_ID bigint,
@auSurveyFieldName varchar(100),
@auSurveyChangedFrom varchar(max),
@auSurveyChangedTo varchar(max),
@auSurveyUpdatedBy varchar(100),
@auSurveyUpdateDate datetime,
@ForWhat int
AS
BEGIN
SET NOCOUNT ON;
IF (@ForWhat=1)
BEGIN
INSERT INTO auSurvey
( Survey_ID
, auSurveyFieldName
, auSurveyChangedFrom
, auSurveyChangedTo
, auSurveyUpdateDate
, auSurveyUpdatedBy
)
VALUES
( @Surv_ID
, @auSurveyFieldName
, @auSurveyChangedFrom
, @auSurveyChangedTo
, @auSurveyUpdateDate
, @auSurveyUpdatedBy
)
END
--ELSE IF (@ForWhat=2)
-- BEGIN
-- INSERT INTO ABC
-- END
END
Example of stored procedure which does not cause trigger to fire:
OK I'm going crazy but if I run this very simple stored procedure directly in DB, the trigger gets fired. But if I run from my C# web, the stored procedure will run since tblSurvey gets updated but trigger won't get fired.
ALTER PROCEDURE [dbo].[spUpdateSurveyDataTest]
AS
BEGIN
Update tblSurvey
Set
[s1FirstName]='YES TRIGGER WORKS for sureYES'
Where Survey_Id=327
END
The point of matter was as I mentioned in the question:
Trigger could not be run from App but I was able to fire it directly from a database update.
The problem was application was connecting to DB with its own generic User which did not have Execute permission for Trigger.
To make things more complicated there was no exception at application level.
The fix was to add
WITH EXECUTE AS OWNER
To the trigger.