Link to home
Get AccessLog in
Avatar of gdspeare
gdspeare

asked on

SQL Trigger looping

I'm using the attached trigger to add an entry into an audit table.  For some reason that I can't see, my trigger is looping twice and creating 4 records in my audit log.  The first 2 records are correct.  I'm getting an Update-New record with the correct value and Update-Old with the correct values.  

The next two records are an update-old and update-new with the new value.

ideas?
USE [UTMSA_BUDGET]
GO
/****** Object:  Trigger [dbo].[trg_Budget_Header_After_Update]    Script Date: 11/10/2010 11:11:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER trigger [dbo].[trg_Budget_Header_After_Update] on [dbo].[tbl_Budget_Header]
AFTER Update
AS

 insert into tbl_Testing (vch_Measure) values ('1' + convert(varchar(23),getdate(),121))

  DECLARE @dtm_AuditTime datetime
  SET @dtm_AuditTime = getdate()

Insert into dbo.tbl_Budget_Header_Audit (
int_RecordNumber_Audit 
,vch_PID 
,dec_Period1 
,dec_Period2 
,dec_Period3 
,dec_Period4 
,dec_Period5 
,dec_Period6 
,dec_Period7 
,dec_Period8 
,dec_Period9 
,dec_Period10 
,dec_Period11 
,dec_Period12 
,vch_UserNote 
,dtm_UpdateDate 
,vch_UpdatedUser 
,vch_Type)

Select
int_RecordNumber
,vch_PID
,dec_Period_1 
,dec_Period_2 
,dec_Period_3 
,dec_Period_4 
,dec_Period_5 
,dec_Period_6 
,dec_Period_7 
,dec_Period_8 
,dec_Period_9 
,dec_Period_10 
,dec_Period_11 
,dec_Period_12 
,vch_UserNote 
,@dtm_AuditTime
,suser_Sname()
,'Update - Old'
From 
	Deleted
Union
/*


Insert into dbo.tbl_Budget_Header_Audit (
int_RecordNumber_Audit 
,vch_PID 
,dec_Period1 
,dec_Period2 
,dec_Period3 
,dec_Period4 
,dec_Period5 
,dec_Period6 
,dec_Period7 
,dec_Period8 
,dec_Period9 
,dec_Period10 
,dec_Period11 
,dec_Period12 
,vch_UserNote 
,dtm_UpdateDate 
,vch_UpdatedUser 
,vch_Type)*/
Select
int_RecordNumber
,vch_PID
,dec_Period_1 
,dec_Period_2 
,dec_Period_3 
,dec_Period_4 
,dec_Period_5 
,dec_Period_6 
,dec_Period_7 
,dec_Period_8 
,dec_Period_9 
,dec_Period_10 
,dec_Period_11 
,dec_Period_12 
,vch_UserNote 
,@dtm_AuditTime
,suser_Sname()
,'Update - New'
From 
	Inserted


/*
-- Step 2: replace the placeholder characters sent from excel
Update tbl_budget_header
set vch_usernote = replace(vch_usernote,'***','''')
*/

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of gplana
gplana
Flag of Spain image

Link to home
membership
This content is only available to members.
To access this content, you must be a member of Experts Exchange.
Get Access
Hi,

Just an idle thought - who says that trigger is wrong? Maybe the trigger is right, and recording what is really happening.

Can I suggest checking the update code? Check that there is only one trigger on this table for instance.

Regards
  David
Hi gplana,

gdspeare is complaining about getting 4 records in his log for each update, not 2.

Regards
  David
Avatar of gdspeare
gdspeare

ASKER

dtodd - yes, i am sure this is the only trigger.  I agree the trigger is doing what I scripted, it just isn't what i expected.

gplana - let me digetst this....it could make some sense.  I ended up changing the trigger to this:
USE [UTMSA_BUDGET]
GO
/****** Object:  Trigger [dbo].[trg_Budget_Header_After_Update]    Script Date: 02/10/2011 12:15:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER trigger [dbo].[trg_Budget_Header_After_Update] on [dbo].[tbl_Budget_Header]
AFTER Update
AS


  -- declare variables
  DECLARE @dtm_AuditTime datetime
  DECLARE @vch_Identity varchar(200)
  DECLARE @Budget_Header_Audit table
  (
	vch_Identity				varchar(200)
	,int_RecordNumber_Audit		int
	,vch_PID 					varchar(10)
	,dtm_UpdateDate				datetime
	,vch_UpdatedUser			varchar(100)
	,dec_Old					decimal(18,4)
	,dec_New					decimal(18,4)
	)

  -- set variables
  SET @vch_Identity = cast(getdate() as varchar(23))
  SET @dtm_AuditTime = getdate()


  -- get the old value
  INSERT INTO @Budget_Header_Audit (
	 vch_Identity
	,int_RecordNumber_Audit 
	,vch_PID 
	,dtm_UpdateDate 
	,vch_UpdatedUser 
	,dec_Old
	,dec_New)
  SELECT
	@vch_Identity
	,int_RecordNumber
	,vch_PID
	,@dtm_AuditTime
	,suser_Sname()
	,dec_period_1 + dec_period_2 + dec_period_3 + dec_period_4 + 
	dec_period_5 + dec_period_6 + dec_period_7 + dec_period_8 +
	dec_period_9 + dec_period_10 + dec_period_11 + dec_Period_12
	,0.00
  FROM 
	Deleted

  -- get the new value
  UPDATE @Budget_Header_Audit SET
	dec_New = dec_period_1 + dec_period_2 + dec_period_3 + dec_period_4 + 
	dec_period_5 + dec_period_6 + dec_period_7 + dec_period_8 +
	dec_period_9 + dec_period_10 + dec_period_11 + dec_Period_12
  FROM
	INSERTED
  WHERE 
	vch_Identity = @vch_Identity

/*
-- Step 2: replace the placeholder characters sent from excel
Update tbl_budget_header
set vch_usernote = replace(vch_usernote,'***','''')
*/


  -- put the record in the audit table
  INSERT INTO dbo.tbl_Budget_Header_Audit (
	int_RecordNumber_Audit,
	vch_PID,
	dtm_UpdateDate,
	vch_UpdatedUser,
	dec_Old,
	dec_New )
  SELECT
	int_RecordNumber_Audit 
	,vch_PID 
	,dtm_UpdateDate 
	,vch_UpdatedUser 
	,dec_Old
	,dec_New
  FROM
	@Budget_Header_Audit
  WHERE
	dec_Old <> dec_New

Open in new window

Have you tried it ?

for me is a little strange: actually you have 2 INSERTs and 2 UPDATEs on your trigger. I feel I don't undrstand your proposal.
I have tested.  I have two inserts and updates into my table variable I'm only inserting 1 record into my audit table.
Hi,

What happened after you changed the trigger?

I agree that it now wont log the second pair of rows. My concern is that they are actual, valid logs from the trigger.

If this table isn't too heavily used, I'd put a waitfor delay '0:00:02' which should enable you to see a little more clearly what is happening with the getdate. At present I figure that you are seeing 4 rows with the exact same datetime ...

HTH
  David
It's strange to make an UPDATE fom the INSERTED table. I would try to UPDATE your variable instead.
Never Use Triggers, Use Stored procedures for all activity, validate all inputs, centralize your business logic.
>>Never Use Triggers, Use Stored procedures for all activity, validate all inputs, centralize your business logic.<<
Isn't that a tad extreme?  TRIGGERs are a very valid tool to be used.  As with any tool in the wong hands, they can be badly used, but to jump to the conclusion that all TRIGGERs should not be used seems excessive.
But to get back to your original question and TRIGGER.  It would seem to me the only plausible reason you are gettng 4 rows added in your original code, is because your UPDATE statement updated 2 rows.  This should be easy to verify.
Nope, Triggers are not a valuable tool.
Introduced in SQL 6, Depricated in SQL 7, Resupporrted in SQL 8 by Demand.

If you are using best practices implementation, that is all access is through stored procedures, then there is no reason to use a trigger. Further, Triggers violate the ACID rule and can cuase committed activity outside the scope of  a transaction. As One member of the SQL Design team said, and I Quote "OOPS"

To diagnose trigger issues, you need have complete access to all the objects and follow the logic carefully. .
  What other triggers exist
  Are there SQL jobs
  Is the data in replication, mirroring?

I have seen triggers completely collapse databases.




>> Triggers are not a valuable tool. <<
We are going to have to agree to disagree.

Now perhaps we had better get back to the question at hand...


acperkins: Here is a perfect example!

1

If the audit insert fails, the value is changed silently

2

If the table is locked, the trigger will fail and may insert null values for notes and budget

3

Since the trigger is fired by an update and updates source table there will be two entry's. maybe more but stopped by system trigger nexting level settings of 2
Check the database for other triggers,
If this trigger has been renamed there might be two running at the same time.
Absolutely!  That is why I stated "As with any tool in the wong hands, they can be badly used".  So here is a case where the code is incorrect and you wish to blame the tool?  I believe there is a saying for that:  Something about a carpenter ...

But we really need to get back to the author and his question...

Feel free to start your own question about the pros and cons of using TRIGGERs.  I am sure that someone will point out to you that they are in Denali and SQL Azure.