Update / Insert trigger in SQL Server

Question

I'm new to SQL Server and triggers. I want to create a trigger that fires when new rows are inserted or existing rows are updated. The trigger will update a set of calculated columns.

Can someone give me a basic trigger that checks if fldType = 'INVALID', if it is, it sets another column errInvalid to 1.

Bare in mind that there will be 5000 rows inserted (in a single hit) per day so performance is an issue. I have looked elsewhere, but just getting confused.

OK, my question has evolved. I've driven down the Persisted Colums route. I've created a succesfull basic persisted field, but having trouble with a nested logic pf.

Can I (and how) do the following:

case when [MissCustName] IS true then when [CustomerAccountName] IS NULL then (1) else (0) end else (0) end

I think that you are confused with what a "calculated column" is. If it where a computed column, then you wouldn't need to update it, it would be automatic without the need of a trigger. It actually seems that this might be the best option for you — Lamak, Jun 12 '12 at 15:45
I can imagine someone opening archives of SO in 20 years and then going "In 2012 inserting 5000 rows per day into a database was a serious issue" ;-) — Michal B., Mar 24 '14 at 08:00

score 3 · Answer 1 · answered Jun 12 '12 at 15:51

3

Maybe better option would be to use computed columns instead..

create table myTab
(
    fieldType varchar(10),
    errInvalid As Case When fieldType = 'INVALID' then 1 else 0 end PERSISTED
)
Go
insert into mytab(fieldType) values ('aaa')
insert into mytab(fieldType) values ('INVALID')
Go
Select * from mytab

answered Jun 12 '12 at 15:51

Jānis

2,216
1
17
27

I did consider calculated fields, but some I would need to carry out some kind of lookup and I don't want to have a performance hit, so thought it better to have static fields that are only changed when I UPDATE or INSERT? – aSystemOverload Jun 12 '12 at 15:54
2

"PERSISTED" keyword is just what you need.. If its "Persisted"- its calculated on insert/update. – Jānis Jun 12 '12 at 15:55
OK, my question has evolved. I've driven down the Persisted Columns route. I've created a successful basic persisted field, but having trouble with a nested logic pf. Can I (and how) do the following: case when [MissCustName] IS true then when [CustomerAccountName] IS NULL then (1) else (0) end else (0) end – aSystemOverload Jun 13 '12 at 10:41
Case When fieldType = 'INVALID' then 1 else Case When otherField = 'value' then 2 else 0 end end /// just like any case statement.. – Jānis Jun 13 '12 at 10:59
GRRRR, think it's having issues referencing another persisted field. Does it calculate persisted fields in order? – aSystemOverload Jun 13 '12 at 11:11
Not sure about that. But what you can do in any case- reference not persisted field, but put the code you have in that persisted field. – Jānis Jun 13 '12 at 11:18
There's a lot of logic to put in one field, hence why I've split it down and then had a master (if A, B or C fail then totalFAIL = TRUE) kinda thing. – aSystemOverload Jun 13 '12 at 11:48

score 1 · Answer 2 · answered Jun 12 '12 at 15:45

You could use something along the lines of this:

CREATE TRIGGER dbo.trgInsertUpdate
ON dbo.YourTableNameHere
FOR INSERT, UPDATE 
AS BEGIN
   -- update the column
   UPDATE dbo.YourTableNameHere
   SET errInvalid = 1
   FROM Inserted i 
   WHERE 
      dbo.YourTableNameHere.ID = i.ID   -- establish link to "Inserted" pseudo-table
      AND fldType = 'INVALID'
END

Basically, the trigger gets called once for each statement, so it might apply to multiple rows at once. Those rows and their new values are stored in the Inserted pseudo-table, which contains all the columns from your table.

You need to find those rows in your table where fldType = 'INVALID', and which are also part of the Inserted pseudo table (those were inserted or updated).

+1 I'll pretend I didn't notice the other 3 edits too. Slow down ;-) — Bridge, Jun 12 '12 at 15:49

score 1 · Answer 3 · answered Jun 12 '12 at 15:46

1

I think this will do what you want:

CREATE TRIGGER Trigger1
ON Table1
FOR INSERT, UPDATE
AS
    UPDATE t
    SET    errInvalid = 1
    FROM   Table1 t
           INNER JOIN Inserted i
             ON i.id = t.id
    WHERE  t.fldType = 'INVALID'

answered Jun 12 '12 at 15:46

Bridge

29,818
9
60
82

score 1 · Answer 4 · edited May 23 '17 at 12:21

1

You really want a calculated column. It will peform better. See this SO post.

CREATE TABLE [dbo].[myTab]
(
  [fldType] [varchar](50) NULL ,
  [errInvalid] AS ( CASE [fldType]
                      WHEN 'Invalid' THEN ( 1 )
                      ELSE ( 0 )
                    END )
)

edited May 23 '17 at 12:21

Community

1
1

answered Jun 12 '12 at 15:52

ssis_ssiSucks

1,476
1
12
11

score 1 · Answer 5 · answered Jun 12 '12 at 15:54

Why not use a "real" computed column?

e.g.:

CREATE TABLE foobar
(
    fldType varchar(10),
    errInvalid AS CASE WHEN fldType = 'INVALID' THEN 1 ELSE 0 END
)

Using a persisted computed column would probably increase performance if you do a lot of reads (pretty much to the level of "physical" columns). Just add PERSISTED after the column definition:

CREATE TABLE foobar
(
    fldType varchar(10),
    errInvalid AS CASE WHEN fldType = 'INVALID' THEN 1 ELSE 0 END PERSISTED
)

Update / Insert trigger in SQL Server

5 Answers5