User specific trigger - 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

General SQL Server Forums

Database Design and Application Architecture

User specific trigger

Author

Topic

RealityMasque
Starting Member

4 Posts

Posted - 2009-03-12 : 06:39:11

Heya,

I'm hoping someone can help me, cuz I don't have the strongest "google-fu" to find my solution otherwise... I have a user table that is accessed by a 3rd party company who can only do inserts & updates. They need a separate user add/update history table to be automatically updated when they change the user table. This made me think trigger. However, I need the trigger to *only* update the user add/update history table when the trigger is invoked the the 3rd party's changes to the user table.

I suppose I could check that a particular field being updated in the user table has a particular new value, but that seems a tad dangerous. I'm hoping that the trigger could actually detect which DB user (i.e., the one that the 3rd party company uses to access my DB) caused the trigger to fire, in order to control when the history table gets new records.

The reason for this limitation is because the user table is updated by a *slew* of different sources (webservices, websites, updates directly to user data in the table, etc.), which other events are already handled...

Thanks in advance...

- O8

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-12 : 06:49:21

Something similar to this?

CREATE TRIGGER	dbo.trgMyTriggerNameHere
ON		dbo.MyTableNameHere
AFTER		INSERT,
		UPDATE
AS

SET NOCOUNT ON

IF SUSER_SNAME() = '#rd Party Client Name here'
	INSERT	MyLogTableNameHere
		(
			col1,
			col2,
			col3
		)
	SELECT	col1,
		col2,
		SUSER_SNAME()
	FROM	inserted

E 12°55'05.63"
N 56°04'39.26"

RealityMasque
Starting Member

4 Posts

Posted - 2009-03-12 : 08:25:37

quote:

Originally posted by Peso

Something similar to this?

CREATE TRIGGER	dbo.trgMyTriggerNameHere
ON		dbo.MyTableNameHere
AFTER		INSERT,
		UPDATE
AS

SET NOCOUNT ON

IF SUSER_SNAME() = '#rd Party Client Name here'
	INSERT	MyLogTableNameHere
		(
			col1,
			col2,
			col3
		)
	SELECT	col1,
		col2,
		SUSER_SNAME()
	FROM	inserted

E 12°55'05.63"
N 56°04'39.26"

Essentially, yes. The IF check is where I'm wondering what I can do. In your example, what is "SUSER_SNAME()"? Does that allow the trigger to know the name of the db user that inserted/updated a user row in the user table which caused the trigger to fire?

- O8

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-12 : 08:34:38

If you read about the SUSER_SNAME in Books Online, what do you learn from that?

E 12°55'05.63"
N 56°04'39.26"

RealityMasque
Starting Member

4 Posts

Posted - 2009-03-12 : 10:35:18

quote:
Originally posted by Peso

If you read about the SUSER_SNAME in Books Online, what do you learn from that?

E 12°55'05.63"
N 56°04'39.26"

Thanks, didn't know about that... I'll check that now... Of course, I have since learned that I might not have to do any of this... hehe...

- O8

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources