Running SQL 2000.

I created the triger below...

CREATE TRIGGER Update_status
ON ACRPLU
AFTER INSERT
AS
Update acrplu set a1updt = 'D' where a1updt = ''

What I am attempting to do is update the a1updt field to 'D' when a new
record is inserted.  The record that is inserted will always be a
blank.  Have I done this correctly??  If this table grows to a couple
of million records, will I have performance problems??  Is there a
better way of doing this??

AHIA,
Larry....

Why not just create a default value on that column and avoid the trigger
altogether?





<lrea***@gmail.com> wrote in message
Show quoteHide quote

I am doing bi-directional replication from an AS/400 to an SQL Server
then the SQL server will replicate out to all 16 locations.  I will
change this trigger at each step in the replication process to identify
at what step the data is at or hung up.  When the data gets to the
correct location, it will be processed by another application then the
status is changed there, then replicated back to the AS/400...  So I
will have a trigger of this nature at all my locations.

Thanks,
Larry...

You might have issues with multiple sessions inserting simultaneously.
Better deal with the rows affected by the firing statement only:

update a
  set a1updt = 'D'
from inserted as i
  join acrplu as a
    on i.keycol = a.keycol

Show quoteHide quote

Try this

Create Trigger Update_Status
On ACRPLU AFTER INSERT
As
    Update A Set A.a1updt = 'D'
        From ACRPLU A
            Where (A.a1updt = '' Or  IsNull(A.a1updt, -1) < 0) And A.PK
In(Select Distinct PK From Inserted)



In this example PK is the primary key on your table. This makes sure that
you update only the records that are being inserted in the table.





<lrea***@gmail.com> escribió en el mensaje
Show quoteHide quote

Thanks for the help.

If Aaron's idea of using a default is not an option (because you don't trust
others to leave the value alone (as can happen when other programmers get
involved) You might also consider an instead of trigger, especially if you
don't have issues with using identity columns and SCOPE_IDENTITY().  It is
kind of tedious but you should not have any performance issues:

CREATE TRIGGER Update_status
ON ACRPLU
INSTEAD OF INSERT
AS
INSERT acrplu ( column1, column2, ... column N, a1updt)
--SELECT column1, column2, ... column N, case when a1updt = '' then 'D' else
a1updt end
SELECT column1, column2, ... column N, 'D'
FROM inserted
go
Either of the SELECT clauses will work, and the one that is not commented
out insures that a 'D" wll be the value stored.

This is what instead of triggers are perfect for, because you basically
intercept the command and resend it.  Nothing else happens.
Show quoteHide quote

Scope of temp table
Why is Count(*) slow?
Bad datetime values stored by SQL Server 2000
Bitwise operations on BigInt
Upsized SQLServer app 100x slower than MsAccess