CODEWITHSUNDEEP
sql-server-2008t-sqltriggers
HoangMN
HoangMN

Reputation: 1

Update/Insert trigger in the same table

I'm kind of new to this but want to write some first simple trigger. If I have mytable with fields A,B,C. When user inserts or updates A and B, I want to update C=A+B by trigger would it be easy? thanks

Upvotes: 0

Views: 3306

Answers (1)

Bridge
Bridge

Reputation: 30651

Here is a trigger that will do as you ask. Since there is a lack of ID column in mytable, there is no way to specify only update c for the rows that have been updated (i.e. the value of c will be recalculated for every row on every insert/update).

CREATE TRIGGER mytrigger
ON mytable
FOR INSERT, UPDATE
AS
    UPDATE mytable
    SET    c = COALESCE(a, 0) + COALESCE(b, 0)

This would be a suitable candidate for a computed column:

CREATE TABLE mytable
  (
     a INT,
     b INT,
     c AS COALESCE(a, 0) + COALESCE(b, 0)
  )

Edit: Example with ID column to only update c for updated/inserted rows:

CREATE TRIGGER mytrigger
ON mytable
FOR INSERT, UPDATE
AS
    UPDATE mytable
    SET    c = COALESCE(a, 0) + COALESCE(b, 0)
    WHERE  ID IN (SELECT ID
                  FROM   inserted)

I've created a SQL Fiddle where you can see this live.

To run you through the SQL Fiddle example:

  1. Create schema, add some sample data, then add the trigger definition (so c is unpopulated at this point).
  2. Select from the table, so you can see the example values.
  3. Update a row, and then select from the table again (you can see c has now been populated).
  4. Insert a new row, then select everything from the table again (c has been populated for this new row).

To explain a bit more about how this works - during trigger execution, SQL server (I'm assuming by now that is what you're using) creates two temporary tables inserted and deleted with copies of the affected rows in - we can join or select from those tables if we need to get data from them; in this case just getting a list of the ids that were affected by the update/insert operation and then only updating c for those rows. For more detail on this, see this MSDN article.

Upvotes: 2

Related Questions