Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

how do I write a single column trigger?

Posted on 2003-12-02
3
Medium Priority
?
223 Views
Last Modified: 2010-08-05
I want to know how to write a trigger so that it fires only after a column value has been changed from a 0 to a 1?

0
Comment
Question by:jayrod
3 Comments
 
LVL 26

Accepted Solution

by:
Hilaire earned 1600 total points
ID: 9859063
create trigger <triggername> on <tablename>
for update
as
if update(<yourcolumn>)
begin
   update whatever you want from <tablename> a inner join inserted b
   on <tablename>.key = inserted.key
   where inserted.<yourcolumn> = 1

end

the if update(<yourcolumn>) will ensure that the inner code is executed only if <yourcolumn> is updated

don't forget that the inserted table may have several rows (case of a massive update eg)
the where clause will allow you to make changes only if the value in <yourcolumn> has been set to 1

Hilaire



0
 
LVL 19

Assisted Solution

by:Dexstar
Dexstar earned 400 total points
ID: 9860455
@jayrod:

> I want to know how to write a trigger so that it fires only after a column
> value has been changed from a 0 to a 1?

Hilaire got you most of the way there, but if you want it to only execute when a column was changed from a 0 to a 1, then you'll need something more like this:
     CREATE TRIGGER YourTable_UpdateTrigger ON YourTable
     FOR UPDATE
     AS
     IF UPDATE( TheColumn )
     BEGIN
          IF ( EXISTS(SELECT * FROM Inserted I INNER JOIN Deleted D ON I.PK = D.PK WHERE D.TheColumn = 0 AND I.TheColumn = 1) )
          BEGIN
               /* Do what you wanted to do here */
          END
     END

I guess I need to know more about WHAT you want to do when the field changes to write it any better than that.

Hope That Helps,
Dex*
0
 
LVL 8

Expert Comment

by:Dishan Fernando
ID: 9864215
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'TR' AND name = 'TrggerName')
      BEGIN
            PRINT 'Dropping Trigger TrggerName'
            DROP  Trigger TrggerName
      END
GO

PRINT 'Creating Trigger TrggerName'
GO

CREATE Trigger TrggerName
      ON TableName
FOR UPDATE

AS

      INSERT INTO OtherTable(col,col...) -- OR Do what ever u want
      FROM
            Inserted
      WHERE  
            OneZeroChangeCol = 1

GO


0

Featured Post

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

782 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question