How to control triggers on a table

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 2005 Forums

Transact-SQL (2005)

How to control triggers on a table

Author

Topic

koolnkool
Starting Member

9 Posts

Posted - 2009-11-17 : 05:30:28

hi all,
i have a update trigger on table1.

table1 will be updated in 2 ways
1. by datatransfer from another table through wizard
2. by sql query.

in case i dont want update trigger to be fired. is it possible?

and can i control trigger depending on columns?
i mean if a table contains x,y and z columns(all are null colmns)and contains insert trigger on this table
i dont to fire insert trigger when i insert data into x,y columns only.
i want to fire insert trigger when i insert data in z column only.
is it possible?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-11-17 : 07:55:12

If I'm reading you correctly, when column z is updated, you want a trigger to fire. You can create a trigger for update and test if z was the column updated

CREATE TRIGGER myTrigger ON myTable
AFTER UPDATE
AS
IF (UPDATE(z))
BEGIN
<do something.
END

Jim

Everyday I learn something that somebody else already knew

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2009-11-17 : 08:12:02

UPDATE() may yield true even if the column's value did not change. It is better to compare the values in the inserted and deleted virtual tables.

update yourtable
set	blah blah blah...
from	yourtable
	inner join inserted on yourtable.primarykey = inserted.primarykey
	inner join deleted on inserted.primarykey = deleted.primarykey
where	inserted.z <> deleted .z

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

rammohan
Posting Yak Master

212 Posts

Posted - 2009-11-18 : 03:21:15

I have created a trigger on schedule table for update

there are two ways in which the schedule table gets updated

schedule table contain empid status timein timeout etc

1 auto updation (tblpunch data transfer to schedule table windows service)

2 schedule chart editor (Where punches can be edit of schedule table of a particular employee and a log is maintained)

in 1 point i dont want the trigger to be called is it possible

One can never consent to creep,when one feels an impulse to soar
RAMMOHAN

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-11-18 : 10:48:52

DISABLE TRIGGER YourTrigger ON YourTable;
-- do insert --
ENABLE TRIGGER YourTrigger ON YourTable;

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2009-11-18 : 12:27:55

If the logic is not to be consistently applied to the data, then it does not belong in a trigger. It belongs in a stored procedure.
Disabling and re-enabling triggers as part of your application invites disaster.

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources