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
– Lamak, Commented Jun 12, 2012 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.
– Michal B., Commented Mar 24, 2014 at 8:00

Jānis · Accepted Answer · 2012-06-12 15:51:10Z

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, 2012 at 15:51

Jānis

2,2661 gold badge17 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

aSystemOverload Over a year ago

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?

Jānis Over a year ago

"PERSISTED" keyword is just what you need.. If its "Persisted"- its calculated on insert/update.

aSystemOverload Over a year ago

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

Jānis Over a year ago

Case When fieldType = 'INVALID' then 1 else Case When otherField = 'value' then 2 else 0 end end /// just like any case statement..

aSystemOverload Over a year ago

GRRRR, think it's having issues referencing another persisted field. Does it calculate persisted fields in order?

|

marc_s · Accepted Answer · 2012-06-12 15:45:55Z

1

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).

answered Jun 12, 2012 at 15:45

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

2 Comments

marc_s Over a year ago

@Bridge: yes - sorry - typing too fast :-) Thanks! Updated.

Bridge Over a year ago

+1 I'll pretend I didn't notice the other 3 edits too. Slow down ;-)

Bridge · Accepted Answer · 2012-06-12 15:46:17Z

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, 2012 at 15:46

Bridge

30.9k10 gold badges64 silver badges85 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 12:21:52Z

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, 2017 at 12:21

CommunityBot

11 silver badge

answered Jun 12, 2012 at 15:52

ssis_ssiSucks

1,5061 gold badge12 silver badges11 bronze badges

Comments

pfigel · Accepted Answer · 2012-06-12 15:54:17Z

1

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
)

answered Jun 12, 2012 at 15:54

pfigel

962 bronze badges

Collectives™ on Stack Overflow

Update / Insert trigger in SQL Server

5 Answers 5

7 Comments

2 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

7 Comments

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related