Solved

how do I write a single column trigger?

Posted on 2003-12-02
3
212 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 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:dishanf
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Addition to SQL for dynamic fields 6 38
Query Syntax 17 34
Sql Server group by 10 27
Need help constructing a conditional update query 16 43
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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.

770 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