Solved

how do I write a single column trigger?

Posted on 2003-12-02
3
215 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 26

Accepted Solution

by:
Hilaire earned 400 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 100 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

751 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