Rick wrote:
> I have add/edit/delete triggers on a SQL Server 2005 table.
>
> Before updating that table, I'd like to be able to set a variable that
> the trigger can read and that only has a lifetime of that connection
> or transaction.
>
> I cannot include the variable in the update stored procedure.
>
> I tried creating a #temptable to hold the value, but it doesn't seem
> to exist from one stored procedure to the next.
>
> Any suggestions.
>
> Thanks,
> Rick

"Rick" <rick@abasoftware.com> wrote in message
news:1141178565.423593.191090@u72g2000cwu.googlegroups.com...
> My triggers create a row in another table.
> I have a column in this other table that gives information
> about the source of the update. This is not known until the
> time of the update. Therefore, I envision a two step process,
>
> 1) first I set a variable inside a transaction or connection
> 2) My triggers read the variable and set the column value in the
> new row they are adding.
>
> What I am asking is what value can I set, that will be limited in scope
> to the transaction or connection, that can be read by the triggers.
>

"David Browne" wrote:

>
> "Rick" <rick@abasoftware.com> wrote in message
> news:1141178565.423593.191090@u72g2000cwu.googlegroups.com...
> > My triggers create a row in another table.
> > I have a column in this other table that gives information
> > about the source of the update. This is not known until the
> > time of the update. Therefore, I envision a two step process,
> >
> > 1) first I set a variable inside a transaction or connection
> > 2) My triggers read the variable and set the column value in the
> > new row they are adding.
> >
> > What I am asking is what value can I set, that will be limited in scope
> > to the transaction or connection, that can be read by the triggers.
> >
>
> Not that this is a terribly good idea, but here's how to do it:
>
> If you create a #temptable in a stored procedure it will be destroyed at the
> end of the procedure call.
>
> If you create the #temptable in a SQL batch, it will live for the lifetime
> of the connection.
>
> EG
>
> create table t(id int primary key, data char(50))
> go
> create trigger t_insert on t for insert
> as
> begin
> if object_id('tempdb..#session') is not null
> begin
> declare @msg varchar(50)
> select @msg = msg from #session
> print @msg
> end
> end
> go
>
> --on login issue this batch
> create table #session
> (
> onerow int primary key check (onerow = 0) default 0,
> msg varchar(50) --add whatever session variables you want
> )
> insert into #session default values
> go
>
>
> --later
> update #session set msg = 'hello'
> go
>
>
> --later still
> insert into t(id, data)
> values (1, 'whatever')
> go
>
>
> David
>
>