Solved

SQL Trigger looping

Posted on 2011-02-10
15
225 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
  • 4
  • 3
  • 3
  • +2
15 Comments
 
LVL 15

Accepted Solution

by:
gplana earned 500 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
T-SQL 10 35
MS SQL: Return all results in a single row separated by commas 1 27
Access join syntax when converting to T-SQL query 4 34
SQL Log size 3 18
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
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 UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

828 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