CODEWITHSUNDEEP
sql-serversql-server-2008t-sql
aSystemOverload
aSystemOverload

Reputation: 3114

Update / Insert trigger in SQL Server

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

Upvotes: 2

Views: 1497

Answers (5)

pfigel
pfigel

Reputation: 96

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
)

Upvotes: 1

ssis_ssiSucks
ssis_ssiSucks

Reputation: 1506

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

Upvotes: 1

Jānis
Jānis

Reputation: 2266

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

Upvotes: 3

Bridge
Bridge

Reputation: 30711

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'

Upvotes: 1

marc_s
marc_s

Reputation: 755361

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

Upvotes: 1

Related Questions