Trigger Not Working TSQL Question

Question

Trigger Not Working TSQL Question

SQL Dude-467553

Hall of Fame

Points: 3895
More actions
March 18, 2010 at 10:09 am

#218022

Hello,
I'm trying to create a trigger on a table that is truncated then we insert all the records from another source each time we load data. I'd like to fill a column called "TEST" with the trigger logic during the data load.
For some reason the trigger is not working, the "Test" column is NULL. I've tested the SELECT statements in the trigger independently and they each work and return the correct results I'm looking for. I also don't see any syntax errors, it parses OK. I've also removed the CHARINDEX part to verify that was not the issue, just leaving the SELECT statments that I tested. That leads me to think that I'm not setting up a trigger correctly using the insert table? Help, or suggestions on articles to read?
CREATE TRIGGER [dbo].[test]
ON [dbo].[Table]
FOR INSERT
AS
INSERT INTO Table (Test)
SELECT
CASE
WHEN CHARINDEX('.', Name) > 0 THEN
LEFT([Name],(CHARINDEX('.',[name])-1))
ELSE
[Name]
END
FROM INSERTED

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

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

Are you bulk importing data? Unless you specify otherwise, bulk imports bypass triggers.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

SQL Dude-467553 Hall of Fame Points: 3895 More actions · Answer 2

Intersting I didn't know that. I looked and the SSIS package was using the Fast load option, I switched that to just use table and the trigger fires now, thank you!!!

Though the trigger seems to insert another record rather than the inserted record.

Example of what happens

Name Test

Joe. NULL

NULL Joe

Fran. NULL

NULL FRAN

SQL Dude-467553 Hall of Fame Points: 3895 More actions · Answer 3

I just realized my question was silly... I need to do an update statement in the trigger instead and join on the inserted table rather than an insert.. doh

But you were right about the bulk insert, so thank you!

GSquared SSC Guru Points: 260824 More actions · Answer 4

You're welcome.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Paul White SSC Guru Points: 150467 More actions · Answer 5

SQL Dude-467553 (3/18/2010)
Intersting I didn't know that. I looked and the SSIS package was using the Fast load option, I switched that to just use table and the trigger fires now, thank you!!!

You do not need to turn off Fast Load in order to fire triggers. In the Advanced Editor for the OLE DB destination component, add FIRE_TRIGGERS to the FastLoadOptions field. Sadly, there is no option in the Basic Editor to do this. See OLE DB Destination - the Fast Load Options section.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

SQL Dude-467553 Hall of Fame Points: 3895 More actions · Answer 6

SQL Dude-467553

Hall of Fame

Points: 3895

March 19, 2010 at 8:10 am

#1136741

Thanks, I'll keep that in mind.

Paul White SSC Guru Points: 150467 More actions · Answer 7

SQL Dude-467553 (3/19/2010)
Thanks, I'll keep that in mind.

I was kinda hoping 2008 would address this, but I have just checked, at it is the same 🙁

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi