Trigger Performance

18 Jan 2006 3:48 PM

__Stephen

I'm not well versed in Triggers, so this should be a straight forward
request.

On a change in a status column, Deactive bit, I want to place the getdate()
in the column DeactiveDate smalldatetime

CREATE TRIGGER deactiveUpDate ON [dbo].[SALES_REP]
FOR UPDATE
AS
declare @KeyId int

IF NOT UPDATE(Deactive)

BEGIN

RETURN

END

SELECT @KeyID = (SELECT SalesRepID FROM Deleted)

update tgi_sales_rep
set DeactiveDate = getdate()
where SalesRepID = @keyId

Is this the best way, or did I miss the boat alltogether by doing another
update on the same table?

TIA

19 Jan 2006 2:12 PM

Dave Frommer

The way you coded the trigger will only work correctly if a single row is
updated. Triggers fire once per update batch, not per row.

try:
CREATE TRIGGER deactiveUpDate ON [dbo].[SALES_REP]
FOR UPDATE
AS

IF UPDATE(Deactive)

BEGIN
update tgi_sales_rep
set DeactiveDate = getdate()
where SalesRepID IN (select SalesRepID FROM deleted )

END

Show quoteHide quote

"__Stephen" <sruss***@transactiongraphics.com> wrote in message
news:eQbs5aEHGHA.3100@tk2msftngp13.phx.gbl...
> I'm not well versed in Triggers, so this should be a straight forward
> request.
>
> On a change in a status column, Deactive bit, I want to place the
> getdate() in the column DeactiveDate smalldatetime
>
>
> CREATE TRIGGER deactiveUpDate ON [dbo].[SALES_REP]
> FOR UPDATE
> AS
> declare @KeyId int
>
> IF NOT UPDATE(Deactive)
>
> BEGIN
>
> RETURN
>
> END
>
> SELECT @KeyID = (SELECT SalesRepID FROM Deleted)
>
> update tgi_sales_rep
> set DeactiveDate = getdate()
> where SalesRepID = @keyId
>
>
> Is this the best way, or did I miss the boat alltogether by doing another
> update on the same table?
>
> TIA
>
>
>

19 Jan 2006 3:24 PM

__Stephen

Show quote Hide quote

"Dave Frommer" <anti@spam.com> wrote in message
news:%233%23MmJQHGHA.1452@TK2MSFTNGP11.phx.gbl...
> The way you coded the trigger will only work correctly if a single row is
> updated. Triggers fire once per update batch, not per row.
>
> try:
> CREATE TRIGGER deactiveUpDate ON [dbo].[SALES_REP]
> FOR UPDATE
> AS
>
> IF UPDATE(Deactive)
>
> BEGIN
> update tgi_sales_rep
> set DeactiveDate = getdate()
> where SalesRepID IN (select SalesRepID FROM deleted )
>
> END

Thanks for the heads up there Dave. This was for just a one-Z use so I have
made the change like yours above for multiple updates if they ever happen.

__Stephen