Solved

how do I write a single column trigger?

Posted on 2003-12-02
3
216 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
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…

624 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