After Insert Trigger not executed

Question

After Insert Trigger not executed

petitpere

SSCrazy

Points: 2520
More actions
August 29, 2014 at 4:02 am

#290180

Hi all,
I have an issue with a trigger on SQLServer 2008R2.
Code is :
create TRIGGER [dbo].[alert_total_cpu_usage_high] ON [dbo].[CounterData]
after insert
AS
BEGIN
raiserror('TEST CPU high', 0, 1) WITH LOG
END
The raiserror statement never gets executed.
If I execute it outside the trigger, it raises the error.
Rows are added to the CounterData table, so that's ok.
I replaced the raiserror statement with an update statement and this one never gets executed as well.
Does anyone know what I'm doing wrong?
Many thanks for your help ...
🙂

Viewing 14 posts - 1 through 13 (of 13 total)

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

venoym SSCarpal Tunnel Points: 4161 More actions · Answer 1

Sounds like you have Triggers and (maybe) Nested Triggers disabled on either the table or the database. I'd check the database configuration first. I don't have SQL Server here to help with the command, but this should give you a starting point.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

How are the rows added to CounterData?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

petitpere SSCrazy Points: 2520 More actions · Answer 3

Thanks for your answers ...

@venonym: I'll try to check whether triggering is enabled for the database/table ...

@gilamonster: the rows are added by the DOS command TYPEPERF with an ODBC connection to the SqlServer. It goes to the database Master. Should this be the issue?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

rot-717018 (8/29/2014)
@venonym: I'll try to check whether triggering is enabled for the database/table ...

There's no table or database setting which disables triggers, don't waste time looking for one.

@GilaMonster: the rows are added by the DOS command TYPEPERF with an ODBC connection to the SqlServer. It goes to the database Master. Should this be the issue?

Maybe. What operation is is using to load the data into SQL?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

petitpere SSCrazy Points: 2520 More actions · Answer 5

GilaMonster (8/29/2014)
.../...
There's no table or database setting which disables triggers, don't waste time looking for one.

OK

I changed the ODBC connection to store the data on a new database, but it doesn't solve the problem.

Neither raiserror nor update are executed.

GilaMonster (8/29/2014)[
@GilaMonster: the rows are added by the DOS command TYPEPERF with an ODBC connection to the SqlServer. It goes to the database Master. Should this be the issue?
Maybe. What operation is is using to load the data into SQL?

No idea; it's a commando from the Windows Monitoring Tools ...seems to be quite often used by DBAs.

I'll try to run a profiler to figure it out ...

petitpere SSCrazy Points: 2520 More actions · Answer 6

Here's the code ...

insert bulk CounterData("GUID" uniqueidentifier,"CounterID" int,"RecordIndex" int,"CounterDateTime" char(24) COLLATE SQL_Latin1_General_CP1_CI_AS,"CounterValue" float,"FirstValueA" int,"FirstValueB" int,"SecondValueA" int,"SecondValueB" int,"MultiCount" int)

petitpere SSCrazy Points: 2520 More actions · Answer 7

I added manually a record to the table but it did not fire the trigger either ?!?

I'm very puzzled!!!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

Yeah, that's what I thought. It's a bulk load (high-speed inserts). The bulk loads (bcp in, BULK INSERT and that Insert bulk) don't fire triggers unless the application running it specifies that triggers must be fired. Nothing you can do on the SQL side, the program running the command has to specify that it wants triggers to be fired.

You'll have to look through the help file for TYPEPERF, see if there's any options you can set to tell it that it must specify the correct option to have triggers fired. Or, the setting may be on the ODBC driver.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

rot-717018 (8/29/2014)
I added manually a record to the table but it did not fire the trigger either ?!?

A manual insert (INSERT INTO) absolutely will fire triggers, no way that a normal insert can not fire a trigger (unless the trigger has been disabled)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Emil B SSCertifiable Points: 5568 More actions · Answer 10

rot-717018 (8/29/2014)
I added manually a record to the table but it did not fire the trigger either ?!?
I'm very puzzled!!!

Hi rot, i know this is a silly question, but are you sure the trigger is enabled? 🙂

ALTER TABLE CounterData ENABLE TRIGGER alert_total_cpu_usage_high

I'm not sure about the command you posted but there is a thing with 'bulk insert' that if FIRE_TRIGGERS is not specified as an argument, no insert triggers execute.

petitpere SSCrazy Points: 2520 More actions · Answer 11

GilaMonster (8/29/2014)
rot-717018 (8/29/2014)
I added manually a record to the table but it did not fire the trigger either ?!?
A manual insert (INSERT INTO) absolutely will fire triggers, no way that a normal insert can not fire a trigger (unless the trigger has been disabled)

It did fire, my fault sorry ... I looked at the old database ...

I'll try to see if there is an option to force the triggers to happen either in typeperf or on the ODBC connection and let you know. Thanks for figuring this out 🙂

petitpere SSCrazy Points: 2520 More actions · Answer 12

emil.bialobrzeski (8/29/2014)
rot-717018 (8/29/2014)
I added manually a record to the table but it did not fire the trigger either ?!?
I'm very puzzled!!!
Hi rot, i know this is a silly question, but are you sure the trigger is enabled? 🙂
ALTER TABLE CounterData ENABLE TRIGGER alert_total_cpu_usage_high
I'm not sure about the command you posted but there is a thing with 'bulk insert' that if FIRE_TRIGGERS is not specified as an argument, no insert triggers execute.

Thanks emil, that's what GilaMonster pointed out; and unfortunatly I need to find out how I can force a Windows utility to fire triggers ...

petitpere SSCrazy Points: 2520 More actions · Answer 13

I did not find a way yet to force typeperf to fire triggers when bulk inserting. Nor did I find a way to tell the ODBC connection to do so.

So I'll take another way to monitor this server over the SQLServer.

Thanks for your help with a special one to Gilamonster!