After Insert Trigger won't fire - Same Trigger exists on 35 other tables working perfectly - Why won't THIS one fire?

Question

Post reply

After Insert Trigger won't fire - Same Trigger exists on 35 other tables working perfectly - Why won't THIS one fire?

marcia.mcblair

Valued Member

Points: 61
More actions
July 23, 2013 at 11:19 am

#295998

AFTER INSERT TRIGGER WON'T FIRE
The same trigger exists on 35 other tables working perfectly.
Why won't this one fire?
USE [UVAHS_Metadata]
GO
/****** Object: Trigger [mdl].[Item_After_Insert_Trgr] Script Date: 07/23/2013 13:14:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Trigger: Item_After_Insert_Trgr
ALTER TRIGGER [mdl].[Item_After_Insert_Trgr] ON [mdl].[Item]
AFTER INSERT
AS
BEGIN
UPDATE mdl.Item
SET Item.Item_Updt_Dtm = GETDATE (),
Item.Item_Add_User_PK =
(SELECT UVAHS_User_PK FROM mdl.UVAHS_User
WHERE (UVAHS_User.UVAHS_User_Id = LTRIM(RTRIM(REPLACE(SYSTEM_USER,'HSCDOM\', ' '))) )),
Item.Item_Updt_User_PK =
(SELECT UVAHS_User_PK FROM mdl.UVAHS_User
WHERE (UVAHS_User.UVAHS_User_Id = LTRIM(RTRIM(REPLACE(SYSTEM_USER,'HSCDOM\', ' '))) ))
FROM inserted i
WHEREItem.Item_PK = i.Item_PK
END

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

i think the issue is really that the trigger is not updating anything, right, and not that the trigger does not fire?

my first guess would be that the inner condition is not being satisfied in that subquery;

is there any data at all in mdl.UVAHS_User so that when this query gets satisfied by finding a row?

SELECT UVAHS_User_PK

FROM mdl.UVAHS_User

WHERE (UVAHS_User.UVAHS_User_Id = LTRIM(RTRIM(REPLACE(SYSTEM_USER, 'HSCDOM\', ' '))))

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

sturner SSC-Insane Points: 22411 More actions · Answer 2

Besides wondering why this is being done in a trigger, I am wondering why this trigger is written in such a way as to assume that every insert is a single row insert... because this one will fail for any multiple row inserts.

edit: well .. maybe not, depending on certain assumptions, but the logic of this methodology is not readily apparent.

The probability of survival is inversely proportional to the angle of arrival.

Lowell SSC Guru Points: 323504 More actions · Answer 3

i'd consider rewriting the trigger to support multiple rows by joining to the tables instead .

something like this looks right to me:

edit: Exactly what sturner was thinking. I'm too slow!, at least i added an example though, might help a bit!

-- Trigger: Item_After_Insert_Trgr

ALTER TRIGGER [mdl].[Item_After_Insert_Trgr] ON [mdl].[Item]

AFTER INSERT

AS

BEGIN

UPDATE MyTarget

SET MyTarget.Item_Updt_Dtm = GETDATE(),

MyTarget.Item_Add_User_PK = u.UVAHS_User_PK

FROM mdl.[Item] MyTarget

INNER JOIN inserted i

ON MyTarget.Item_PK = i.Item_PK

LEFT OUTER JOIN mdl.UVAHS_User u

ON u.UVAHS_User_Id = LTRIM(RTRIM(REPLACE(SYSTEM_USER, 'HSCDOM\', ' ')))

END

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

sturner SSC-Insane Points: 22411 More actions · Answer 4

Yeah, there is something sort-of fishy about a design that would incorporate that kind of [il]logic in a trigger. But without seeing more information about this entire design there is not much more we can suggest.

The probability of survival is inversely proportional to the angle of arrival.

Lowell SSC Guru Points: 323504 More actions · Answer 5

sturner (7/23/2013)
Yeah, there is something sort-of fishy about a design that would incorporate that kind of [il]logic in a trigger. But without seeing more information about this entire design there is not much more we can suggest.

agreed; for an audit like this,i would just store the SYSTEM_USER(well a suite of various user-related columns actually) regardless, and if i need the matching Id from a table, that's a separate report...

it's going round and round to other table when it's not needed at all.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

sturner SSC-Insane Points: 22411 More actions · Answer 6

Lowell (7/23/2013)
it's going round and round to other table when it's not needed at all.

And... the OP said this trigger is on 35 other tables. :w00t:

The probability of survival is inversely proportional to the angle of arrival.