September 25, 2003 at 9:02 am
Can a table with a trigger be updated from a stored proc without firing the trigger?
Thanks
September 25, 2003 at 10:37 am
No. About the only workaround I can think of is to insert the SP's process ID (@@SPID)in a table and rewrite the trigger to check to see if its SPID is in the table to conditionally bypass its usual actions.
You cannot use @@PROCID for this, as that will have the ID of the trigger, not the calling sp, while the trigger is executing.
--Jonathan
--Jonathan
September 25, 2003 at 1:30 pm
I agree with Jonathan in that you could do several things in the trigger to allow preferential treatment, using things like host_name() or suser_sname() to qualify what to allow through.
HTH
Ray Higdon MCSE, MCDBA, CCNA
------------
Ray Higdon MCSE, MCDBA, CCNA
September 25, 2003 at 1:35 pm
We may add a column to the table and then test within the trigger code the value in that column as to whether we continue processing the code or drop out of it.
Somebody heard that it was possible in Oracle to update a table and specify to bypass the trigger and that this ability may exist in SQL but it doesn't look that way.
Thanks for the workarounds.
September 25, 2003 at 3:19 pm
A little bell went off the first time I replied, and I finally remembered something new in SQL Server 2000. There is now a "free" column, context_info, in sysprocesses that is available for purposes like this. So you could do something like this in the stored procedure:
SET CONTEXT_INFO 0xA
--Do your nasty stuff to the table
SET CONTEXT_INFO 0
And revise the trigger thusly:
ALTER TRIGGER u_MyTable ON MyTable FOR UPDATE AS
IF NOT (SELECT context_info
FROM master.dbo.sysprocesses
WHERE spid = @@SPID) = 0xA
BEGIN
--Old trigger code here
END
--Jonathan
--Jonathan
September 25, 2003 at 3:54 pm
If you are not replicating the table you can use:
ALTER TABLE foo DISABLE TRIGGER trg_foo
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
September 26, 2003 at 3:18 am
if you want to keep it simple, and take care of cleaning up yourself, you could just define and set a global variable in your stored proc, check the value of this variable inside your trigger and either continue processing or exit.
c.
September 26, 2003 at 5:13 am
quote:
We may add a column to the table and then test within the trigger code the value in that column as to whether we continue processing the code or drop out of it.
quote:
If you are not replicating the table you can use:ALTER TABLE foo DISABLE TRIGGER trg_foo
These methods would allow another concurrent process to bypass the trigger...
quote:
if you want to keep it simple, and take care of cleaning up yourself, you could just define and set a global variable in your stored proc, check the value of this variable inside your trigger and either continue processing or exit.
Global variable?
--Jonathan
Edited by - jonathan on 09/26/2003 05:30:48 AM
--Jonathan
September 26, 2003 at 5:33 am
Using SET CONTEXT_INFO is a nice way to do this, just have to be careful that you don't break anything else that might be using it too. A different way to do the same thing is when they open the connection, run a proc that pushes the spid and whatever info you need into a table, then reference that in the trigger. Both of these techniques come in handy for other situations too.
Andy
September 26, 2003 at 5:57 am
Thanks all, great stuff!
We're running SQL7 so we don't have the CONTEXT_INFO variable.
The DISABLE/ENABLE trigger looks like the tidiest one to implement since so many stored procs touch the table in question and only 1 app needs to get around the trigger.
September 26, 2003 at 7:00 am
quote:
We're running SQL7 so we don't have the CONTEXT_INFO variable.
The DISABLE/ENABLE trigger looks like the tidiest one to implement since so many stored procs touch the table in question and only 1 app needs to get around the trigger.
But, as I stated above, any other SPs running concurrently with the SP that disables the trigger will also not have the trigger in effect.
I like Andy's idea; wish I had thought of that. 😉
I did think of another hack that might work with an update trigger. If the table has a primary key that is never updated (or, better yet, is referenced via DRI), update that value to itself in your proc that needs to bypass the trigger.
Then use IF NOT UPDATED(PKColName) in the trigger to bypass the rest of the trigger.
--Jonathan
--Jonathan
September 26, 2003 at 7:19 am
"But, as I stated above, any other SPs running concurrently with the SP that disables the trigger will also not have the trigger in effect."
Great point Jonathan...that kills that fix.
September 26, 2003 at 1:20 pm
Also, altering table to disable to trigger means that you need to have sufficient permissions to do so. If your application can only read or write data, then disabling trigger through "alter table" will not work.
September 30, 2003 at 11:42 am
quote:
I agree with Jonathan in that you could do several things in the trigger to allow preferential treatment, using things like host_name() or suser_sname() to qualify what to allow through.
SQL Server 7 introduced Database Roles. These roles let you define groups of logins and assign various permissions for them. You may also choose to use them to make application decisions.
The simplest approach to your problem would seem to make use of this feature. Create a role for the application login that should bypass the trigger. In the trigger, use IS_MEMBER to test for the role. If the result is 1, the current user is in the database role. You can then alter the behavior of the trigger accordingly.
You can see the syntax of the IS_MEMBER function in Books on Line or at MSDN:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ia-iz_97oy.asp
Trigger example
CREATE TRIGGER [TRG_IU_TBL] ON [DBO].[TBL]
FOR INSERT, UPDATE
AS
BEGIN
DECLARE @ISMEM INT
SET @ISMEM = IS_MEMBER ('my_database_role_that_should_not_fire_trigger')
IF @ISMEM = 0 -- not a member
BEGIN
--TRIGGER CODE HERE
END
END
It is a privilege to see so much confusion. -- Marianne Moore, The Steeplejack
It is a privilege to see so much confusion. -- Marianne Moore, The Steeplejack
September 30, 2003 at 11:54 am
quote:
It is a privilege to see so much confusion.
My thought exactly. OP was looking for a way to bypass a trigger for a specific stored procedure, not an application or user.
--Jonathan
--Jonathan
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply