CODEWITHSUNDEEP
sqlsql-servertriggerssql-update
ghoston3rd
ghoston3rd

Reputation: 149

Insert trigger will not work properly

I am trying to create a database trigger that will update certain characters in a field for a table when a user inserts data into the table... Ex.

ID  EXCHANGE   LEADRT
1    new         L-3
2    new         3
3    new         5

So I would want to leave id 1 alone because the format for the LEADRT is correct but ids 2 and 3 are not.

CREATE TRIGGER triggerupdate ON PoleUnits FOR INSERT, 
UPDATE AS 
if not exists (select * from Poleunits where LEADRT like '%L-%') 
update PoleUnits set LEADRT = STUFF (LEADRT, 1, 0,'L-');

Any ideas why I can't get this to work or better suggestions on how to accomplish this?

Upvotes: 3

Views: 149

Answers (1)

SWeko
SWeko

Reputation: 30882

In insert and update triggers you have access to a specific table called inserted where the rows to be inserted/updated are held. Those are not real tables, they are just logical tables with the same structure as the table on which the trigger fired.

Your current logic works on the original table, thus working with all the existing data, but not with the data you are actually inserting, i.e. it will update everything except the data you actually want updated. Something like this could work:

CREATE TRIGGER triggerupdate ON PoleUnits 
FOR INSERT, UPDATE AS 
  update PoleUnits
  set LEADRT = STUFF (PoleUnits.LEADRT, 1, 0,'L-')
  from PoleUnits 
    inner join inserted  -- this is basically a self join
      on PoleUnits.ID = inserted.ID
  where PoleUnits.LEADRT not like '%L-%'

This will only update those rows in PoleUnits that are being inserted, and only if their LEADRT field is not in the L- format.

Upvotes: 3

Related Questions