Insert without fire trigger

Question

Post reply

Insert without fire trigger

herman_d

Old Hand

Points: 312
More actions
August 6, 2003 at 1:30 am

#80779

Is it possible if I want to use insert statement without firing trigger ?
If, yes, how ?

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

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

mccork SSC-Insane Points: 22288 More actions · Answer 1

Use the ALTER TABLE statement to disable the trigger, then do the INSERT, then enable the trigger again.

Additionally BULK INSERT by default will not fire triggers.

Cheers,

- Mark

Cheers,
- Mark

NPeeters SSChampion Points: 12536 More actions · Answer 2

If you use the ALTER TABLE technique, be aware that any other inserts taking place (by another user, for example) will also not fire the trigger!

herman_d Old Hand Points: 312 More actions · Answer 3

Yes, that's why I'm prefer to use ALTER TABLE.

Actually, I'm more prefer to use BULK INSERT, but it only work for external data file, not from table within the same database.

If I force to use it, too many jobs to be done (I have to push these data into a string delimiter file, and pull back into my database)

After some review, I've decided to add a column for a comment/flag, to point the row is trigger fired or not.

I think this is a foolish thing I should do to my database, but this is the simple way I should passed.

quote:
If you use the ALTER TABLE technique, be aware that any other inserts taking place (by another user, for example) will also not fire the trigger!

cneuhold Hall of Fame Points: 3375 More actions · Answer 4

be aware that altering a table and disabling a trigger is DDL and cannot be part of a transaction!

another possibility would be (yes - ok, hit me!) a global variable that gets checked first thing in your trigger to see if it should execute or not.

best regards,

chris.

mccork SSC-Insane Points: 22288 More actions · Answer 5

Now that Chris has mentioned it, I do recall having seen situations where people have coded "bypass" facilities into triggers. In a case I recall, the session's CONTEXT_INFO was used as the "global variable", combined with a check that it's a sysadmin.

The sort of code in the trigger was:

 

IF (SELECT context_info

    FROM master.dbo.sysprocesses

    WHERE spid = @@spid) = 0x427970617373496E7365727454726967676572

    AND is_srvrolemember('sysadmin') = 1 -- Make sure non-sysadmins don't bypass triggers

    << bypass the trigger processing >>

and, to exploit this code:

 

SET CONTEXT_INFO 0x427970617373496E7365727454726967676572

.

.

do inserts

Cheers,

- Mark

Cheers,
- Mark

herman_d Old Hand Points: 312 More actions · Answer 6

Thanks all.

It was amazing if I'm using global variable....