[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 357
  • Last Modified:

Record update trigger doesn't work when multiple records are updated in batch

Ok, I'm not really sure how to explain this so I will try to be as clear as possible, but bear with me.. I'm sorta new to SQL syntax.  Essentially I have a trigger that updates some stuff on insert / change of one of my tables.  It works fine when I update a single row but gives an error when I update multiple rows.  It seems that this is because I use 'IN' instead of a join but I can't seem to wrap my mind around how this would be done with a join, so any help is greatly appreciated.  Here is the query that works with the single update.

      IF(SELECT TrigDate FROM INSERTED) IS NOT NULL
      BEGIN
      
            UPDATE RecordsTable SET AudDate = (SELECT TrigDate FROM INSERTED), StrtVal = 'WHATEVER'
            WHERE UID = (SELECT UID FROM DELETED)
      
      END
0
jclemo
Asked:
jclemo
  • 4
1 Solution
 
pivarCommented:
Hi,

Is this what you need

IF EXISTS (SELECT 1 FROM INSERTED WHERE TrigDate IS NOT NULL) BEGIN
  UPDATE RecordsTable SET AudDate = i.TrigDate, StrtVal = 'WHATEVER'
     FROM RecordsTable r, INSERTED i
            WHERE r.UID = i.UID
            AND i.TrigDate IS NOT NULL
END

/peter
0
 
pivarCommented:
Sorry you don't have to check for null in TrigDate twice, just use

  UPDATE RecordsTable SET AudDate = i.TrigDate, StrtVal = 'WHATEVER'
     FROM RecordsTable r, INSERTED i
            WHERE r.UID = i.UID
            AND i.TrigDate IS NOT NULL
0
 
pivarCommented:
By the way, the table DELETED is only used with UPDATE/DELETE not INSERT
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
thiyagukCommented:
I agree with pivar solution., but, to handle multiple updates pivar's second query is correct :


  UPDATE RecordsTable SET AudDate = i.TrigDate, StrtVal = 'WHATEVER'
     FROM RecordsTable r, INSERTED i
            WHERE r.UID = i.UID
            AND i.TrigDate IS NOT NULL



or Use Cursor to iterate through the records
as in


Like 
Open cursor cur for select * from inserted

Open in new window

0
 
pivarCommented:
I wouldn't recommend cursors here. You should use cursors only if there isn't another solution since they are much slower and more resourcedemanding.
0
 
dportasCommented:
Don't use cursors in triggers. Updates/Inserts/Deletes are set-based so trigger code should be too.

Actually I think it's best practice not to put ANY data-modifying code in triggers. Use triggers only to enforce business rules (without updates) or if you can't modify the data access code for some reason. Do your updates in a stored procedure instead.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now