SQL Update Trigger Question

[somacore]

Hello all,

I am trying to implement a trigger on a Microsoft SQL Server 2000 database. I have created the trigger, and it works mostly like it should, however I have a slight problem I can't seem to get around.

We use two fields for this trigger. Both fields are 3 digit numbers (varchar(3) in the database, I have no control over that). The second field is used to store the value of the first field only if the first field has changed, and only the first time this is done. By default, both fields are blank. The second field is read-only.

For example, if I put the number 555 into the first field, I want that number populated in the second field, so it reads 555. If I then change the first field to 444, I want the second field to remain 555, and so on, no matter how many times field 1 is changed.

My trigger is able to update the second field, but I can't figure out the logic needed to store the value of field 1 in field 2 only the first time field 1 is updated.

The trigger is below. Field 1 is identified as C536871310, Field 2 is C536870924:

USE [RSystem]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[MRUpdate]

ON [dbo].[T311]

FOR update

AS

Declare @MR varchar(3)

Declare @OrigMR varchar(3)

Declare @Ticket varchar(15)



If Update(C536871310)

Begin

Select @MR = (Select C536871310 from deleted)

Select @OrigMR = (Select C536871310 from inserted)

Select @Ticket = (Select C1 from inserted)

if @MR = NULL
update T311 set T311.C536870924 = @OrigMR WHERE C1 = @Ticket
else
update T311 set T311.C536870924 = @MR WHERE C1 = @Ticket
End
GO

[ck9663]

Although your trigger can be optimized, by doing a single UPDATE statement, I won't change it. So far you did good.

The problem is in this part

"if @MR = NULL"

You can not use equal sign to check for null. Use IS NULL or IS NOT NULL instead.

Read more here


-- CK

[somacore]

Thank you, that seemed to fix the issue.

I wonder why the parser didn't catch that? I was having a non sql moment apparently.