i have several triggers which under certain circumstances i dont want to
run. I would like to be able to set a variable against the sql connection
which i can test for within my trigger and then prevent running if certain
conditions apply. Is this possible? without using connection specific
tables? is it even possible with them?
--
TimBHi Tim,
Can you explain why you don't want the triggers to run? If it is for a
maintenance or administration task you can turn the triggers off with ALTER
TABLE <table name> DISABLE TRIGGER ALL, and enable them later with ALTER
TABLE <table name> ENABLE TRIGGER ALL. Note that this will disable the
triggers for all users, not just the current connection, and that you need
to be a member of the sysadmin, db_ddladmin or db_owner roles to use it.
If you want to do it in a different scenario, triggers might not be the
right solution and you can be better off putting your code in a stored
procedure. Can you give some more information in that case?
Jacco Schalkwijk
SQL Server MVP
"timb" <timb@.test.com> wrote in message
news:eVUlJnM4DHA.2804@.TK2MSFTNGP11.phx.gbl...
quote:
> Hi,
> i have several triggers which under certain circumstances i dont want
to
quote:|||There are certain routines within my vb app which need to skip certain
> run. I would like to be able to set a variable against the sql connection
> which i can test for within my trigger and then prevent running if certain
> conditions apply. Is this possible? without using connection specific
> tables? is it even possible with them?
> --
> --
> TimB
>
sections of a trigger as they cater for the code elsewhere.
I have tried to use a temporatry table however how do i check that the table
exists befreo selecting from it. i.e. to prevent an error being raised when
the table is not present.
I have also found that you can create a @.@. variable but cannot currently get
it to persist (if i am using it correctly) and there is no help about
creating these.
Is it possible to create a connection specific variable?
thanks in advance
TimB
--
TimB[490134]
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:OA0TQHN4DHA.2348@.TK2MSFTNGP09.phx.gbl...
quote:
> Hi Tim,
> Can you explain why you don't want the triggers to run? If it is for a
> maintenance or administration task you can turn the triggers off with
ALTER
quote:|||Not that I recommend the following solution, but we implemented it to avoid
> TABLE <table name> DISABLE TRIGGER ALL, and enable them later with ALTER
> TABLE <table name> ENABLE TRIGGER ALL. Note that this will disable the
> triggers for all users, not just the current connection, and that you need
> to be a member of the sysadmin, db_ddladmin or db_owner roles to use it.
> If you want to do it in a different scenario, triggers might not be the
> right solution and you can be better off putting your code in a stored
> procedure. Can you give some more information in that case?
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "timb" <timb@.test.com> wrote in message
> news:eVUlJnM4DHA.2804@.TK2MSFTNGP11.phx.gbl...
want[QUOTE]
> to
connection[QUOTE]
certain[QUOTE]
>
(temporarily) having to rewrite a rather large chunk of the application:
CREATE TABLE dbo.session_role (
spid int NOT NULL ,
role_id int NOT NULL ,
CONSTRAINT PK_session_context PRIMARY KEY CLUSTERED ( spid ),
CONSTRAINT FK_session_context_role FOREIGN KEY ( role_id ) REFERENCES
dbo.role_definitions ( id )
)
END
GO
CREATE PROCEDURE SetSessionRole @.nRoleID INT
AS
SET NOCOUNT ON
IF EXISTS(SELECT * FROM session_role WHERE spid = @.@.spid)
UPDATE session_role
SET role_id = @.nRoleID
WHERE spid = @.@.spid
ELSE
INSERT INTO session_role (spid, role_id)
VALUES (@.@.spid, @.nRoleID)
GO
You can now check in stored procedures, views and triggers for the role_id
with SELECT role_id FROM session_role WHERE spid = @.@.spid.
As I said before, this is not a good structural solution, but deadlines were
looming etc. Rewrite your application code at the earliest opportunity
possible.
Jacco Schalkwijk
SQL Server MVP
"timb" <timb@.test.com> wrote in message
news:%23bTatMN4DHA.2440@.TK2MSFTNGP09.phx.gbl...
quote:
> There are certain routines within my vb app which need to skip certain
> sections of a trigger as they cater for the code elsewhere.
> I have tried to use a temporatry table however how do i check that the
table
quote:
> exists befreo selecting from it. i.e. to prevent an error being raised
when
quote:
> the table is not present.
> I have also found that you can create a @.@. variable but cannot currently
get
quote:|||Hi Jacco,
> it to persist (if i am using it correctly) and there is no help about
> creating these.
> Is it possible to create a connection specific variable?
> thanks in advance
> TimB
> --
> --
> TimB[490134]
> "Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
> news:OA0TQHN4DHA.2348@.TK2MSFTNGP09.phx.gbl...
> ALTER
need[QUOTE]
> want
> connection
> certain
>
thanks for the help. I was thinking of something similar however
using temporary tables i.e. #
what i was going to do was create the temporary table in the area where the
first transaction occurs and then delete the table or other such logic in
the specific part of the trigger which may be called as a result of the
first transaction.
Only thing is how do i check for the presence of this temporary table?
i.e.
Create Table #Stocktake
(
stocktake bit
)
insert into #stocktake values (1)
if exists (select * from tempdb.dbo.sysobjects where id =
object_id(N'[dbo].[#stocktake]')) print 'table exists'
i cannot get the statement to print and i want to trap the error which will
occur in the trigger when it is called from transactions which dont have the
code to create the temporary table.
thanks in advance
TimB
--
TimB[490134]
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:%23CmZHiN4DHA.1948@.TK2MSFTNGP12.phx.gbl...
quote:
> Not that I recommend the following solution, but we implemented it to
avoid
quote:
> (temporarily) having to rewrite a rather large chunk of the application:
> CREATE TABLE dbo.session_role (
> spid int NOT NULL ,
> role_id int NOT NULL ,
> CONSTRAINT PK_session_context PRIMARY KEY CLUSTERED ( spid ),
> CONSTRAINT FK_session_context_role FOREIGN KEY ( role_id ) REFERENCES
> dbo.role_definitions ( id )
> )
> END
> GO
> CREATE PROCEDURE SetSessionRole @.nRoleID INT
> AS
> SET NOCOUNT ON
> IF EXISTS(SELECT * FROM session_role WHERE spid = @.@.spid)
> UPDATE session_role
> SET role_id = @.nRoleID
> WHERE spid = @.@.spid
> ELSE
> INSERT INTO session_role (spid, role_id)
> VALUES (@.@.spid, @.nRoleID)
> GO
> You can now check in stored procedures, views and triggers for the role_id
> with SELECT role_id FROM session_role WHERE spid = @.@.spid.
> As I said before, this is not a good structural solution, but deadlines
were
quote:|||> Only thing is how do i check for the presence of this temporary table?
> looming etc. Rewrite your application code at the earliest opportunity
> possible.
>
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "timb" <timb@.test.com> wrote in message
> news:%23bTatMN4DHA.2440@.TK2MSFTNGP09.phx.gbl...
> table
> when
> get
ALTER[QUOTE]
> need
it.[QUOTE]
the[QUOTE]
specific[QUOTE]
>
Try:
IF OBJECT_ID('tempdb..#Stocktake') IS NOT NULL
PRINT 'table exists'
Hope this helps.
Dan Guzman
SQL Server MVP
"timb" <timb@.test.com> wrote in message
news:O8NWaqP4DHA.3596@.TK2MSFTNGP11.phx.gbl...
quote:
> Hi Jacco,
> thanks for the help. I was thinking of something similar however
> using temporary tables i.e. #
> what i was going to do was create the temporary table in the area where
the
quote:
> first transaction occurs and then delete the table or other such logic in
> the specific part of the trigger which may be called as a result of the
> first transaction.
> Only thing is how do i check for the presence of this temporary table?
> i.e.
>
> Create Table #Stocktake
> (
> stocktake bit
> )
> insert into #stocktake values (1)
>
> if exists (select * from tempdb.dbo.sysobjects where id =
> object_id(N'[dbo].[#stocktake]')) print 'table exists'
>
> i cannot get the statement to print and i want to trap the error which
will
quote:
> occur in the trigger when it is called from transactions which dont have
the
quote:
> code to create the temporary table.
>
> thanks in advance
> TimB
>
> --
> --
> TimB[490134]
> "Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
> news:%23CmZHiN4DHA.1948@.TK2MSFTNGP12.phx.gbl...
> avoid
role_id[QUOTE]
> were
raised[QUOTE]
currently[QUOTE]
a[QUOTE]
with[QUOTE]
> ALTER
the[QUOTE]
you[QUOTE]
> it.
> the
stored[QUOTE]
dont[QUOTE]
> specific
>
No comments:
Post a Comment