Connection specific variable?

Hi,
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...
> Hi,
> 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?
> --
> --
> TimB
>|||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
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...
> 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
> 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...
> > Hi,
> > 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?
> >
> > --
> > --
> > TimB
> >
> >
>|||Not that I recommend the following solution, but we implemented it to avoid
(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...
> 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
> 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...
> > 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
> > 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...
> > > Hi,
> > > 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?
> > >
> > > --
> > > --
> > > TimB
> > >
> > >
> >
> >
>|||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
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...
> Not that I recommend the following solution, but we implemented it to
avoid
> (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...
> > 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
> > 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...
> > > 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
> > > 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...
> > > > Hi,
> > > > 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?
> > > >
> > > > --
> > > > --
> > > > TimB
> > > >
> > > >
> > >
> > >
> >
> >
>|||> Only thing is how do i check for the presence of this temporary table?
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...
> 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
> 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...
> > Not that I recommend the following solution, but we implemented it to
> avoid
> > (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...
> > > 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
> > > 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...
> > > > 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
> > > > 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...
> > > > > Hi,
> > > > > 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?
> > > > >
> > > > > --
> > > > > --
> > > > > TimB
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>