After Insert Trigger Problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Other SQL Server 2008 Topics

After Insert Trigger Problem

Author

Topic

Feras82
Starting Member

3 Posts

Posted - 2012-05-10 : 03:51:25

Hello,
I have the following scenario
I wrote a very simple test trigger

Here is the code:

ALTER TRIGGER [dbo].[Test2]
ON [dbo].[Messages]
AFTER insert
AS
BEGIN
declare @id int
select @id = id from inserted
update smsserver.dbo.messages set smsserver.dbo.messages.body = left(smsserver.dbo.messages.body, len(smsserver.dbo.messages.body)-3)
from smsserver.dbo.messages
where smsserver.dbo.messages.id = @id
END

The trigger simply update a value of some column after inserting, but actually the trigger is blocking inserting until I disable it, then the id filed (identity) will take a very big unexpected value depending on the period between inserting the record and disabling the trigger, which gives the sense that something is going into infinite loop until disabling.

Regards,

Syrian4ever

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-10 : 07:44:35

I don't see anything in your trigger that would cause it to blocking insert. Do you have any update triggers on the table? If you do, is recursive triggers turned on your database? By default it is off, but you can check using the following query:

SELECT is_recursive_triggers_on FROM sys.databases WHERE NAME = 'thedatabasename'

While the following may not help you in solving the problem you should change your code to the following:

ALTER TRIGGER [dbo].[Test2]
ON [dbo].[Messages] 
AFTER insert
AS 
BEGIN
UPDATE smsserver.dbo.messages
SET    smsserver.dbo.messages.body = LEFT(
           smsserver.dbo.messages.body,
           LEN(smsserver.dbo.messages.body) -3
       )
FROM   smsserver.dbo.messages
	INNER JOIN INSERTED ON INSERTED.id = smsserver.dbo.messages
END

It is logically the same if only one row is inserted in an insert statement, but will allow the correct behavior if multiple rows are inserted.

Feras82
Starting Member

3 Posts

Posted - 2012-05-13 : 02:01:13

Thank you Sunitabeck
The value of SELECT is_recursive_triggers_on FROM sys.databases WHERE NAME = 'thedatabasename' is 0

and I had used your code, but unfortunetly I still have the same issue.

Syrian4ever

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-13 : 15:28:39

why do you need to do this via trigger?
also does your table have a clustered index?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources