Re: Trigger, alternative way to pass variable to trigger
From: Louis Davidson (dr_dontspamme_sql_at_hotmail.com)
Date: 04/20/04
- Next message: SQL Apprentice: "Re: how to shrink tempdb"
- Previous message: Eric Sabine: "Re: Need help with Tricky SELECT statements please."
- In reply to: hngo01: "Re: Trigger, alternative way to pass variable to trigger"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 20 Apr 2004 16:20:20 -0500
You don't have to execute the setUser command in the master database, it
will become a system stored procedure. You wouldn't use this in a procedure,
rather as an additional piece of code you will call when you are calling
your procedure. The idea is to completely minimize coding needed inside
your code, if you were going to call it in the procedure, you will have to
pass it to the procedure.
Say you were going to call your procedure.
You could build a string, like:
exec usp_updatecomp 'value', 'value',...
and execute it. Instead, just add on:
exec sp_setuser 'bob'
exec usp_updatecomp 'value', 'value',...
The overhead will be negligible, and then you have the value set and
available for use in your triggers, so you can have:
create trigger trigger name ....
as
update table
set updateBy = dbo.getuser()
from table
join inserted
on <keys>
I would personally build an object that was the only object my program used
to call stored procedure or execute sql commands and let it handle it the
details of this on an ongoing basis..
-- ---------------------------------------------------------------------------- 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:1c9e01c4270c$93af0c30$a301280a@phx.gbl... > 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: SQL Apprentice: "Re: how to shrink tempdb"
- Previous message: Eric Sabine: "Re: Need help with Tricky SELECT statements please."
- In reply to: hngo01: "Re: Trigger, alternative way to pass variable to trigger"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
