UPDATE trigger firing on INSERT

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 2000 Forums

SQL Server Development (2000)

UPDATE trigger firing on INSERT

Author

Topic

melberti
Starting Member

10 Posts

Posted - 2005-08-03 : 09:43:05

I am trying to create two triggers, one for update and one for insert. Either one by itself works fine, but as soon as I add the second, the first stops working. I currently have ONLY an update trigger, but I can see by the error message I'm getting on record insert that this trigger is being called.

How can I keep the update trigger from being called on insert? And why is it being called in the first place?

Thanks.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-03 : 09:45:59

Can you post the code you used?

Madhivanan

Failing to plan is Planning to fail

melberti
Starting Member

10 Posts

Posted - 2005-08-03 : 09:50:51

ALTER TRIGGER updateParticipant ON dbo.PARTICIPANT_TABLE
FOR UPDATE
AS

DECLARE @id_event int
DECLARE @id_old_event int
DECLARE @id_member int
DECLARE @status int
DECLARE @modified_by int

SELECT @id_event = ID_EVENT, @id_member = ID_MEMBER, @status = ID_PARTICIPANT_STATUS, @modified_by = ID_MODIFIED_BY from inserted

--need this to fire for only for updates where the event number changes
--in addition to the following insert
IF UPDATE(ID_EVENT)
BEGIN
SELECT @id_old_event = ID_EVENT from deleted

INSERT INTO PARTICIPANT_STATUS_LOG
(ID_MEMBER, ID_EVENT, ID_PARTICIPANT_STATUS, ID_CREATED_BY)
VALUES
(@id_member, @id_old_event, 6, @modified_by)

END

--need this to fire for all updates
IF UPDATE(ID_PARTICIPANT_STATUS)
BEGIN

INSERT INTO PARTICIPANT_STATUS_LOG
(ID_MEMBER, ID_EVENT, ID_PARTICIPANT_STATUS, ID_CREATED_BY)
VALUES
(@id_member, @id_event, @status, @modified_by)
END

GO

---------------
---------------
CREATE TRIGGER insParticipant] ON dbo.PARTICIPANT_TABLE
FOR INSERT
AS

DECLARE @id_event int
DECLARE @id_member int
DECLARE @status int
DECLARE @created_by int

SELECT @id_event = ID_EVENT, @id_member = ID_MEMBER, @status = ID_PARTICIPANT_STATUS, @created_by = ID_CREATED_BY from inserted

INSERT INTO PARTICIPANT_STATUS_LOG
(ID_MEMBER, ID_EVENT, ID_PARTICIPANT_STATUS, ID_CREATED_BY)
VALUES
(@id_member, @id_event, @status, @created_by)

GO

X002548
Not Just a Number

15586 Posts

Posted - 2005-08-03 : 10:02:50

quote:
Originally posted by melberti

How can I keep the update trigger from being called on insert? And why is it being called in the first place?

You probably installed the miracle engine...disable it....

Or post the code of your triggers and your tables. Follow the link below.

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

X002548
Not Just a Number

15586 Posts

Posted - 2005-08-03 : 10:07:39

OK, First problem you have is that you are not treating the triggers as set based. They do not fire one row at a time, but rather as an entire set.

Second, please post ALL of the table DDL so we can replicate the entire scenario.

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

melberti
Starting Member

10 Posts

Posted - 2005-08-03 : 10:17:47

After some more investigation I figured out the problem... I had an UPDATE statement in my INSERT SP -- long story -- but that's what was causing the UPDATE trigger to fire.

Thanks for your replies!

X002548
Not Just a Number

15586 Posts

Posted - 2005-08-03 : 11:40:35

You still have a MAJOR problem in that you are only handling 1 of potentialy n rows every time the trigger fires.

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

melberti
Starting Member

10 Posts

Posted - 2005-08-03 : 11:45:56

Don't think so because there are constraints on the table preventing more than one record for same member and event.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources