Solved

SQL Trigger looping

Posted on 2011-02-10
15
227 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 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
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.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

729 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