Global Variable in SQL Server

Hi,

I have a question on SQL Server.

How do I have a value that passing from application to Stored Proc. Then the variable will pass from stored proc to the trigger without storing into any table.

I have done by using declaring #TempTable on StoredProc and use it in Triggers but it doesn't work. Anyone know any alternative?

Please help.

Thanks

If those passed parameters are already inserted/updated on your table then you can access those values from the trigger using INSERTED table. (Note Inserted table only accessable from the Trigger Scope & it will have the same table structure as the main table).

Inside Your Trigger:

Code Snippet

Declare @.SomeValue as Varchar(100);

Select @.SomeValue = SomeColumn From Inserted;

It is not good idea to use Gloabal Variable / Temp Table on triggers. You can't say the values always inserted from your SP.

|||

No. the passed parameter I do not want to stored in the table due to some reason. How can I pass the value from stored proc to triggers without storing into table?

|||

You cannot

pass the value from stored proc to triggers

without storing the data in the table.

All input data for the TRIGGER MUST exist within the TABLE that the TRIGGER fires on.

Of course, if you do not wish that the data be kept in the database for concern about security/visibility, you could have the TRIGGER set the field to NULL -thereby obliterating the data that was initially input.

|||Can we do something on tempdb? I mean can I stored the value into tempdb in stored proc then retrieve it from tempdb in triggers?|||

According to you,

I have done by using declaring #TempTable on StoredProc and use it in Triggers but it doesn't work.

Did it work?

|||

The following example may help you...

Code Snippet

Create Table ThisIsIt (
[Id] int,
[Value] varchar(100))

go

Create Table ThisIsLog (
[Users] varchar(100),
[When] datetime,
[Id] int,
[Operation] int,
[ValuesAffected] Varchar(8000))

go

Create Trigger trg_ThisIsIt_logger on ThisIsIt For Insert
as
Begin
Declare @.ValuesAffected as Varchar(8000);
Declare @.User as varchar(100);
Declare @.Id as Int;

Select @.ValuesAffected = '"?1";"?2"'
Select @.Id = Id, @.ValuesAffected = Replace(Replace(@.ValuesAffected,'?1', Id),'?2',Value)
From Inserted
If Exists(Select ID From tempdb..Sysobjects Where id = Object_id('tempdb..#Info'))
Select @.User = [User] from #Info;

Insert Into ThisIsLog Values
(@.User, getdate(), @.Id, 1, @.ValuesAffected)
End

go

Create proc InsertThisIsIt
(
@.Id int,
@.Value varchar(100),
@.User varchar(100)
)
as
Begin
Create table #Info
(
[User] varchar(100)
)

Insert Into #Info Values(@.User);

Insert Into ThisIsIt Values(@.Id, @.Value);
End