How can i send a parameter to Trigger? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server Development (2000)

How can i send a parameter to Trigger?

Author

Topic

CLages
Posting Yak Master

116 Posts

Posted - 2004-02-27 : 08:56:05

Hi. Is there any way to send some fields to be checked when triggers is fired ?

My needs is: I have to update some fields, but first i have to check
some flags, and this flags doenst have in anyt table.

tks

ex. If X = 01
update ......
else
if X = 05
Update ....
etc.

Carlos Lages
Using MS-SQL with PowerCobol(www.adtools.com)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-02-27 : 12:32:00

With a trigger, you know what rows have just been updated. They are stored in the deleted or inserted tables. That's actually the name of them when inside the trigger. So if X is part of the row that was updated, then you can get the info from the deleted and inserted tables.

Tara

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-02-27 : 12:47:32

...in a manner something like this...:

DECLARE
@Number int

SET @Number = (SELECT number FROM inserted)
-- or like this
SET @Number = (SELECT number FROM deleted)

(just wanted to make it 100% clear what to do)

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-02-27 : 12:48:55

Well, to add to that then you'll want to have a WHERE statement on those selects. You can't assume that only one row is in the inserted or deleted table.

Tara

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-02-27 : 13:06:39

Hmm, but woun't the trigger run once for every insert?? And I thougth the inserted and deleted-tables were only relative to the scope of the insert/update/delete...maybe I need to read up on this...

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-02-27 : 13:08:09

No, it doesn't run after each row. It runs on an entire statement. So if this deletes 10 rows:

DELETE FROM Table1
WHERE ColumnC = 0

Then the trigger will fire after the delete completes and 10 rows will be put in the deleted table.

Tara

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-02-27 : 14:19:38

Excellent to learn new things every day, even when I'm totally hung over and basically want to die... :)

safigi
Starting Member

15 Posts

Posted - 2004-02-27 : 16:45:24

try this:
create table a(i int)
go
create trigger a_trigger on a
for insert
as
declare @b varbinary(100)
select @b=context_info from master.dbo.sysprocesses where spid=@@spid

select cast(@b as varchar(100))
go

create proc test
as
declare @b varbinary(100)

set @b=cast('test' as varbinary)
SET CONTEXT_INFO @b
insert into a values (1)

quote:
Originally posted by CLages

Hi. Is there any way to send some fields to be checked when triggers is fired ?

My needs is: I have to update some fields, but first i have to check
some flags, and this flags doenst have in anyt table.

tks

ex. If X = 01
update ......
else
if X = 05
Update ....
etc.

Carlos Lages
Using MS-SQL with PowerCobol(www.adtools.com)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources