[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 232
  • Last Modified:

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

0
gdspeare
Asked:
gdspeare
  • 4
  • 3
  • 3
  • +2
1 Solution
 
gplanaCommented:
When an update trigger fires, it has records on both inserted and deleted tables. Inserted table has new values and deleted table has old values.

Your trigger is inserting the row inserted and the row deleted because you insert the result of two selects with a union.

Hope it helps.
0
 
David ToddSenior DBACommented:
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
0
 
David ToddSenior DBACommented:
Hi gplana,

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

Regards
  David
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
gdspeareAuthor Commented:
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

0
 
gplanaCommented:
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.
0
 
gdspeareAuthor Commented:
I have tested.  I have two inserts and updates into my table variable I'm only inserting 1 record into my audit table.
0
 
David ToddSenior DBACommented:
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
0
 
gplanaCommented:
It's strange to make an UPDATE fom the INSERTED table. I would try to UPDATE your variable instead.
0
 
BanthorCommented:
Never Use Triggers, Use Stored procedures for all activity, validate all inputs, centralize your business logic.
0
 
Anthony PerkinsCommented:
>>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.
0
 
Anthony PerkinsCommented:
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.
0
 
BanthorCommented:
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.




0
 
Anthony PerkinsCommented:
>> 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...


0
 
BanthorCommented:
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.
0
 
Anthony PerkinsCommented:
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.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now