Re: Trigger, alternative way to pass variable to trigger
From: hngo01 (hngo01_at_hotmail.com)
Date: 04/20/04
- Next message: Rich Wallace: "Touchy Trigger"
- Previous message: Tom Moreau: "Re: Stored Procedure Question"
- In reply to: Louis Davidson: "Re: Trigger, alternative way to pass variable to trigger"
- Next in thread: Louis Davidson: "Re: Trigger, alternative way to pass variable to trigger"
- Reply: Louis Davidson: "Re: Trigger, alternative way to pass variable to trigger"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 20 Apr 2004 12:20:44 -0700
Assumed that I have SP_SETUSER 'myuser_ID' in the MASTER
DATABASE!
These are my actual SP's: Where do I place SP_SETUSER ??
INSIDE my actual SP?
CREATE PROCEDURE dbo.usp_UpdateComp
@EndingValue varchar(50),
@Component varchar(50),
@TestCode varchar(50),
@Type varchar(100)
AS
UPDATE tblCompType SET
Component=@Component,
TestCode=@TestCode,
Type=@Type
WHERE EndingValue=@EndingValue
GO
CREATE PROCEDURE dbo.usp_DeleteComp
@EndingValue varchar(50)
AS
DELETE
FROM tblCompType
WHERE EndingValue=@EndingValue
RETURN 0 --OK
GO
>-----Original Message-----
>What we did was have our object that we used to call
stored procedures add
>the following call:
>
>exec sp__setuser 'application user name' --call to set
context.
>exec actualStoredProcedure @parm1, @parm2, ..., @parmN
>
>The stored procedure was more or less just the following:
>
>create procedure setuser --may want to name it sp_ and
put this in the
>master database
>(
> @userName varchar(128)
>) as
>
> begin
> declare @var varbinary(128)
> set @var = cast(@userName as varbinary(128))
> SET CONTEXT_INFO @var
> end
>go
>
>The username value is not a column, it was just a text
value that your
>application knows your user as. We had a million plus
potential users, so
>we didn't want to actually create user names for them in
our database
>security system, so they authenticated, then just sent us
their name.
>
>Then in the trigger or default constraints, I used a user
defined function
>to get the value back.
>
>--you can look in many books or websites to find out how
to make this a
>system function, though just calling it as master.. or
whatever is fine too.
>create function getuser()
>returns varchar(128)
>as
> begin
> declare @var varchar(128)
> select @var = cast(context_info as varchar(30))
> from master..sysprocesses
> where spid = @@spid
>
> return @var
> end
>go
>
>You can now use dbo.getuser() in triggers and constraints
just like you
>would use a system function.
>--
>----------------------------------------------------------
------------------
>Louis Davidson (drsql@hotmail.com)
>Compass Technology Management
>
>Pro SQL Server 2000 Database Design
>http://www.apress.com/book/bookDisplay.html?bID=266
>
>Note: Please reply to the newsgroups only unless you are
>interested in consulting services. All other replies
will be ignored :)
>
>"hngo01" <hngo01@hotmail.com> wrote in message
>news:197601c426ec$d55b6310$a601280a@phx.gbl...
>> Your feedback is appreciated.
>> But where specifically I would define or set
CONTEXT_INFO
>> so it starts of every batch of my server??
>> AND when I use [set @var = cast('username' as varbinary
>> (128))] is that [username] is system variable or any
>> variable that I want?
>>
>>
>>
>> >-----Original Message-----
>> >There is no simple way, but I have done this once using
>> the context_info
>> >that is part of the sysprocesses table. It is a binary
>> value, but you can
>> >easily convert store your user names in there.
>> >
>> >So at the start of every batch to your server, you
make a
>> call to:
>> >
>> >declare @var varbinary(128)
>> >set @var = cast('username' as varbinary(128))
>> >SET CONTEXT_INFO @var
>> >
>> >Now you have your global value that you can use.
>> >
>> >select cast(context_info as varchar(30))
>> >from master..sysprocesses
>> >where spid = @@spid
>> >
>> >I suggest tacking it on every batch in case you are
using
>> connection pooling
>> >(everyone shares the same connection, so you can never
>> determine who is who,
>> >so you reset the value every time.) If not, you can
>> simply put this as part
>> >of your connection routine.
>> >
>> >--
>> >-------------------------------------------------------
--- >> ------------------ >> >Louis Davidson (drsql@hotmail.com) >> >Compass Technology Management >> > >> >Pro SQL Server 2000 Database Design >> >http://www.apress.com/book/bookDisplay.html?bID=266 >> > >> >Note: Please reply to the newsgroups only unless you are >> >interested in consulting services. All other replies >> will be ignored :) >> > >> >"hngo01" <hngo01@hotmail.com> wrote in message >> >news:194a01c426de$3fc13b80$a401280a@phx.gbl... >> >> Hi all, >> >> >> >> I am using a trigger to do a transaction log - keep >> >> tracking who add/update/delete data. The problem >> >> that I have is: I use [system_user] from SQL-SERVER to >> log >> >> user id but in my application (VB) I use a generic >> account >> >> to login SQL-SERVER. So if I have 10 clients login the >> SQL- >> >> SERVER using this account, using this [system_user] >> can't >> >> differentiate who add/delete/update data. In my VB >> client >> >> application I have user id that I can pass to SQL- SERVER >> >> but my understanding is that trigger doesn't allow >> >> parameters. >> >> Is there a way to pass variable to trigger? Or is there >> a >> >> way to get user id if client application use the same >> >> generic account from SQL-SERVER? >> >> >> > >> > >> >. >> > > > >. >
- Next message: Rich Wallace: "Touchy Trigger"
- Previous message: Tom Moreau: "Re: Stored Procedure Question"
- In reply to: Louis Davidson: "Re: Trigger, alternative way to pass variable to trigger"
- Next in thread: Louis Davidson: "Re: Trigger, alternative way to pass variable to trigger"
- Reply: Louis Davidson: "Re: Trigger, alternative way to pass variable to trigger"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
