Trigger When Row Changes

3 Sep 2006 6:24 PM

Stephen Lynch

I am trying to set up a trigger where when the last or first name field
changes, it will posts the persons EmployeeUID to another table. I have it
working except that it sends all of the records to the destination table,
not just the single record that changed. Any ideas?

CREATE TRIGGER Ins_EmpUpFlag

ON

Employees

AFTER INSERT,UPDATE

AS

IF UPDATE(First) OR Update(Last)

BEGIN

INSERT INTO EmpUpdateFlag

(EmployeeUID)

Select EmployeeUID

FROM Employees

END

Thanks

Steve

3 Sep 2006 7:05 PM

Aaron Bertrand [SQL Server MVP]

> FROM Employees

Try:

FROM inserted

3 Sep 2006 7:40 PM

Stephen Lynch

Aaron:

Thanks, that worked. Can I use a count to see if that EmployeeUID is
already in the table, and if so terminate. I tried this:

CREATE TRIGGER Ins_EmpUpFlag
ON
Employees
AFTER INSERT,UPDATE
AS
IF UPDATE(First) OR Update(Last)
AND (SELECT count(*) AS 'Number of Rows' FROM EmpUpdateFlag
Where (Select EmployeeUID FROM inserted))=0
BEGIN
INSERT INTO EmpUpdateFlag
(EmployeeUID)
Select EmployeeUID
FROM Employees
END

But I get this message.
Msg 4145, Level 15, State 1, Procedure Ins_EmpUpFlag, Line 8

An expression of non-boolean type specified in a context where a condition
is expected, near ')'.

Show quote

"Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message
news:%23wzBXv4zGHA.1252@TK2MSFTNGP04.phx.gbl...
>> FROM Employees
>
> Try:
>
> FROM inserted
>

3 Sep 2006 7:54 PM

Aaron Bertrand [SQL Server MVP]

IF UPDATE(First) OR UPDATE(Last)
BEGIN
    INSERT EmpUpdateFlag(EmployeeUID)
        SELECT EmployeeUID
        FROM inserted i
        WHERE NOT EXISTS
        (
            SELECT 1
                FROM EmpUpdateFlag
                WHERE EmployeeUID = i.EmployeeUID
        )
END

Show quote

"Stephen Lynch" <raider1rai***@yahoo.com> wrote in message
news:44fb2fb7$0$3577$815e3792@news.qwest.net...
> Aaron:
>
> Thanks, that worked. Can I use a count to see if that EmployeeUID is
> already in the table, and if so terminate. I tried this:
>
> CREATE TRIGGER Ins_EmpUpFlag
> ON
> Employees
> AFTER INSERT,UPDATE
> AS
> IF UPDATE(First) OR Update(Last)
> AND (SELECT count(*) AS 'Number of Rows' FROM EmpUpdateFlag
> Where (Select EmployeeUID FROM inserted))=0
> BEGIN
> INSERT INTO EmpUpdateFlag
> (EmployeeUID)
> Select EmployeeUID
> FROM Employees
> END
>
> But I get this message.
> Msg 4145, Level 15, State 1, Procedure Ins_EmpUpFlag, Line 8
>
> An expression of non-boolean type specified in a context where a condition
> is expected, near ')'.
>
>
>
>
>
>
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in
> message news:%23wzBXv4zGHA.1252@TK2MSFTNGP04.phx.gbl...
>>> FROM Employees
>>
>> Try:
>>
>> FROM inserted
>>
>
>

3 Sep 2006 7:19 PM

Hari Prasad

Hi,

Instead of using Employee table whicle inserting into EmpUpdateFlag table
use the virtual table INSERTED.
Inserted table will just contain the new value being inserted or Updated.

Use the below code.

CREATE TRIGGER Ins_EmpUpFlag
ON
Employees
AFTER INSERT,UPDATE
AS
IF UPDATE(First) OR Update(Last)
BEGIN
        INSERT INTO EmpUpdateFlag
        (EmployeeUID)
        Select EmployeeUID
        FROM INSERTED
END

Thanks
Hari
SQL Server MVP

Show quote

"Stephen Lynch" <raider1rai***@yahoo.com> wrote in message
news:44fb1dfa$0$3575$815e3792@news.qwest.net...
>I am trying to set up a trigger where when the last or first name field
>changes, it will posts the persons EmployeeUID to another table. I have it
>working except that it sends all of the records to the destination table,
>not just the single record that changed. Any ideas?
>
> CREATE TRIGGER Ins_EmpUpFlag
>
> ON
>
> Employees
>
> AFTER INSERT,UPDATE
>
> AS
>
> IF UPDATE(First) OR Update(Last)
>
> BEGIN
>
> INSERT INTO EmpUpdateFlag
>
> (EmployeeUID)
>
> Select EmployeeUID
>
> FROM Employees
>
>
> END
>
>
>
> Thanks
>
>
>
> Steve
>
>

3 Sep 2006 10:39 PM

Erland Sommarskog

Stephen Lynch (raider1rai***@yahoo.com) writes:

> I am trying to set up a trigger where when the last or first name field
> changes, it will posts the persons EmployeeUID to another table. I have
> it working except that it sends all of the records to the destination
> table, not just the single record that changed. Any ideas?
>...
> IF UPDATE(First) OR Update(Last)

In addition to the other posts, note that all that IF UPDATE says is
that these columns appeared in the SET clause of the UPDATE statement,
not that the values were actually changed. If that is necessary, you
need to join inserted and deleted to see which for rows there have
been a change.

And keep in mind that many rows can be updated at one time, and the
trigger will fire once.

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx