Trigger Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Trigger Help

Author

Topic

naylor67
Starting Member

2 Posts

Posted - 2002-10-14 : 13:45:11

Group:

I have a trigger that is used to "update" a backup table when the main table has been modified. The problem I am having is I have a user in the database that I do not want to be able to cause the fire the trigger. For example, if the user "tim" is logged into the system and makes an update to the main table I want those changes to fire the trigger, but if "joe" updates the main table the trigger will see that joe was the user logged in and it will ignore the trigger. See the script below.

Thanks in Advance
Bob

The trigger so far looks like the following:

IF EXISTS (SELECT 1 FROM sysobjects WHERE name = 'updateProductWG')
DROP TRIGGER updateProductWG
GO
PRINT 'Creating Trigger updateProductWG..(ver=000)'
GO

CREATE TRIGGER updateProductWG on pdt
/* ver=000 */

FOR UPDATE
AS
IF UPDATE (abbr) OR UPDATE (name)

BEGIN
SET NOCOUNT ON
UPDATE wastegoal
SET wastegoal.abbr = inserted.abbr,
wastegoal.name = inserted.name
FROM inserted, wastegoal
WHERE inserted.pdtno = wastegoal.pdtno

END

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-10-14 : 14:23:56

Have you tried looking at USER?

Jay White
{0}

naylor67
Starting Member

2 Posts

Posted - 2002-10-14 : 14:28:34

Jay:

I am kindof new at all this TSQL scripting. Could you help me out by pointing me in the right direction?

thanks
bob

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-10-14 : 17:09:16

What Jay is saying is that USER is a SQL keyword that tells you who is logged in (up to a point). You might want to use SUSER_SNAME() instead. Just put this in a IF...BEGIN...END clause in your trigger.

IF SUSER_SNAME() <> 'joe' BEGIN
...
END

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-10-14 : 17:46:39

You can also take a look at ALTER TABLE in sql server books online help to find out how to turn enable and disable triggers on a table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources