?
Solved

SQL Trigger looping

Posted on 2011-02-10
15
Medium Priority
?
228 Views
Last Modified: 2012-05-11
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
Comment
Question by:gdspeare
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
  • +2
15 Comments
 
LVL 15

Accepted Solution

by:
gplana earned 2000 total points
ID: 34864421
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
 
LVL 35

Expert Comment

by:David Todd
ID: 34864457
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
 
LVL 35

Expert Comment

by:David Todd
ID: 34864482
Hi gplana,

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

Regards
  David
0
Industry Leaders: 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!

 

Author Comment

by:gdspeare
ID: 34864498
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
 
LVL 15

Expert Comment

by:gplana
ID: 34864560
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
 

Author Comment

by:gdspeare
ID: 34864581
I have tested.  I have two inserts and updates into my table variable I'm only inserting 1 record into my audit table.
0
 
LVL 35

Expert Comment

by:David Todd
ID: 34864611
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
 
LVL 15

Expert Comment

by:gplana
ID: 34864698
It's strange to make an UPDATE fom the INSERTED table. I would try to UPDATE your variable instead.
0
 
LVL 10

Expert Comment

by:Banthor
ID: 34865435
Never Use Triggers, Use Stored procedures for all activity, validate all inputs, centralize your business logic.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 34867999
>>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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 34868021
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
 
LVL 10

Expert Comment

by:Banthor
ID: 34872790
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 34876575
>> 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
 
LVL 10

Expert Comment

by:Banthor
ID: 34876820
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 34876862
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

Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question