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

SQL Server Development (2000)

Trigger help

Author

Topic

jrebocho
Starting Member

3 Posts

Posted - 2007-05-10 : 11:01:27

I 'm trying to make an update trigger. I need to look at a status field and see if it has been update to a certain phrase to run the rest of the trigger. How is that done?

Thanks for the help
John

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-10 : 11:15:39

[code]IF UPDATE(<column_name>)
-- do something[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

jrebocho
Starting Member

3 Posts

Posted - 2007-05-10 : 11:19:23

I know that part, but how do I only do it if it has a certain word in the field? There are different statuses that can appear like pending , in Que and finished and failed. I need to do something if it hits failed not the others.

Thanks

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-10 : 11:21:28

What you want to do if status is failed?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

jrebocho
Starting Member

3 Posts

Posted - 2007-05-10 : 11:25:15

I need to delete detail records from 2 other tables. where the id = the same as the trigger table.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-10 : 11:31:01

[code]Delete t1
From table1 t1 join inserted i on t1.id = i.id
where i.status = 'Failed'

Delete t2
From table2 t2 join inserted i on t2.id = i.id
where i.status = 'Failed'[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

mobasha
Starting Member

45 Posts

Posted - 2007-05-10 : 17:51:30

i think this may help if what i have understand was right
'create trigger tr_update_'+@database+'_'+@table+' on '+@table+
' for update
as '+
@declare_statemen+' begin '+
@from_inserted+'
if(@after_tran_status=''t'')
begin
update '+@table+' set befor_tran_status=''u'',update_date=getdate()
where '+@where_statemen+'
end
else
begin
select @befor_tran_status=i.befor_tran_status from inserted as i
begin
if(@befor_tran_status=''u'')
update '+@table+' set update_date=getdate()
where '+@where_statemen+'
else
update '+@table+' set insert_date=getdate()
where '+@where_statemen+'
end
end

end'

MobashA

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-11 : 01:46:50

Why everything dynamic? That doesn't look like even a valid dynamic sql!

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

mobasha
Starting Member

45 Posts

Posted - 2007-05-14 : 15:15:15

first when i used this text i needed to create 200 trigger on each table so am not going to write all be my self the logic is the same but the tables and the fields r diferent,
second just look on the logic if u find it helpful its ok if not it is not a problems ..each have there cases and ways??

MobashA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources