January 30, 2002 at 1:40 pm
Does inserted and deleted tables not support text datatypes? If not is there a way to get the old and new value of them somehow?
Thanks
January 30, 2002 at 3:37 pm
Triggers in SQL Server 7 and AFTER triggers in SQL 2K do not support text or ntext data types. INSTEAD OF triggers for SQL 2K do. Here's more:
http://www.sqlservercentral.com/columnists/bkelley/triggerswhatsnew.asp
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
February 1, 2002 at 9:37 am
Hi Brian,
Thanks for the link. I did it wihtout the text column. But that was a very interesting article. Also was wondering after reading your article that it will be a bit of performance problem ...
Thanks for the information
February 1, 2002 at 9:54 am
Triggers can always pose a performance problem and I'm not a real big fan of them. I use them when I have to for auditing purposes or when I have to enforce business or integrity rules (a 1 to 3 relationship, for instance, can't be inforced any other way).
With respect to text, text columns are often stored in separate pages apart from the rest of the record. As a result, you're not looking in just one place any longer. While I've not seen any articles detailing the magnitude of the performance hit, surely there is some sort of extra processing.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
February 1, 2002 at 10:11 am
February 1, 2002 at 10:23 am
Hi Brian,
I am sure you proably had a need or done this in the past and decided I am goin to ask you a design question.
You might have figured out that I am working on a generic piece of code which builds triggers on the tables they have selected and basically populates a trasaction table.
The problem is there are certain tables which do not ahve the user id. Therefore I cannot log "who changed that transaction in its respective table" onto the transaction table.
The client app I use is PB. I thought maybe I can use the dbparm where I can tag on the user id who logs in as say "abc" and then pass it somehow to the trigger.
Now is this workable? How can I pass this user id to the trigger on that table? Initally I thought I will pass the machine id then the username but in my case It will not work. I have to come up with an alternative and I decided the dbparm way where I tack on the user id and then somehow pass it to the trigger?
I hope I have explained my case here properly if not please ask me if you think you need more information.
Much thanks
February 1, 2002 at 2:14 pm
When the database is accessed by this application, does each user have to have permissions to access the database, or is it a shared account?
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
February 1, 2002 at 2:50 pm
Well I know this is not the best way but this is how it is configured.
1) Each user has a certain rights to the app. Now this information is all stored int he database. It does not use the MSSQL servers system security. Well I think they did it for reason so it is db independant. Therefore a user named "jack" has to have access to the app to do certain things.
Now once the user has logged in after the app doing all the securiyt checks then he is logged in as a genric login say "abc" which has aliased as a dba.
Thanks
February 1, 2002 at 2:56 pm
Ouch. If you've got shared logins, the system functions which help you identify users don't work. With respect to passing information to the triggers, if the information isn't available in the inserted or deleted tables (or the original table), unfortunately, that's not going to be possible. What you are looking at is probably having to add code to your stored procedures or expand your queries to carry out the auditing manually. I have a friend dealing with a similar issue (generic audit methods), so I'm discussing the issue back and forth with him. If someone doesn't come up with a better idea in the meantime, I'll let you know what we finally settled on.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
February 1, 2002 at 3:07 pm
Hi Brain,
Is there a session variable concept in SQl server which I can access later. like here is my plan.
I can set a SQl server session variable through the dbparm and access it via the trieg like a select @var= session variable and then use it as the user id.
Is this doable or has the weekend fatigue set in:)
thanks
February 1, 2002 at 3:38 pm
not doable. You could insert some value into a table and match the @@spid with the user on login.
Steve Jones
February 1, 2002 at 3:52 pm
Hi Steve/Brian;
Or how about this? Say I store it in the sysprocess in the hostname throguh the dbparm from PB and then in my trigger code
"SELECT hostname FROM master.dbo.sysprocesses WHERE program_name = 'myprog'"
Wont this work?
Thanks
February 1, 2002 at 9:33 pm
Instead of that you might look at HOST_NAME() which will return the same information. The problem there is you are auditing the computer name and not the user logged in. Which means if Mary typically uses a computer, but for some reason Louis uses Mary's computer, the audit is going to look like Mary made the action.
What I've been discussing with my friend is creating a permanent table for auditing purposes. In your case it would have two columns: one for SPID and one for the username.
If you're passing the username to a stored procedure, the first thing you can do is insert into this table both the SPID of the connection (@@SPID) and the username you've passed. Then, your trigger can pull the record from the table, do the insert for the audit, then delete based on SPID.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
February 4, 2002 at 10:15 am
I like Brian's suggestion.Just be sure that you are updating the table correctly on login. If a connection fails and another logs in, it could recieve the same SPID as the failed connection, so your login process needs to be bulletproof.
Steve Jones
February 4, 2002 at 11:44 am
Hi Brian/Steve,
Thanks! I am doing as per your suggestion and it seems fine so far.
Thanks guys.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply