take record which cause firing the Trigger

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

New to SQL Server Programming

take record which cause firing the Trigger

Author

Topic

vgunse@gmail.com
Starting Member

6 Posts

Posted - 2009-05-26 : 03:17:47

Hi,
I am new to sqlserver 2000. pls suggest things i doing right here..

i want to take the particular record inside the trigger, which cause to fire the trigger(the record inserted while firing a trigger)

I searched over the web, i came to know there are two logical tables in sqlserver 2000 named INSERTED/DELETED
But i executed (select * from INSERTED) , but i am getting error(Invalid object name 'inserted'.)

inside my trigger:

ALTER TRIGGER trigger_name
ON dbo.myTable
FOR INSERT
AS
DECLARE @userId nvarchar(100)
select @userId=i.userid from inserted i
......

The above trigger fired on inserting a record in USER table which contains USERID,USERNAME columns

Regards,
vgunse

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-26 : 03:42:15

You have the wrong approach. You should not rely on that only one record is inserted at any given time.

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

vgunse@gmail.com
Starting Member

6 Posts

Posted - 2009-05-26 : 04:28:46

quote:
Originally posted by Peso

You have the wrong approach. You should not rely on that only one record is inserted at any given time.

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

I tried with the following also:
select @userId=i.userid from(select top 1 * from inserted i order by rowid desc)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-26 : 04:40:08

No no no...

CREATE TRIGGER...

INSERT Target
SELECT i.Col1
FROM inserted AS i
INNER JOIN AuxTable AS e ON e.pkCol = i.pkCol

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

vgunse@gmail.com
Starting Member

6 Posts

Posted - 2009-05-26 : 05:05:26

Thanks for your immediate reply,

i can't understand this ,please briefly explain this:

INSERT Target
SELECT i.Col1
FROM inserted AS i
INNER JOIN AuxTable AS e ON e.pkCol = i.pkCol

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-26 : 05:18:47

You shouldn't assign a value from inserted into a variable since there is no guarantee that only one record will be inserted!
Make the trigger code SET-BASED.

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

vgunse@gmail.com
Starting Member

6 Posts

Posted - 2009-05-26 : 06:10:53

Thanks again for your good support,
i got record from table INSERTED

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources