CODEWITHSUNDEEP
sql-servertriggers
Gerrit Botes
Gerrit Botes

Reputation: 57

Creating a simple FOR INSERT trigger on a table

Using Microsoft SQL Server Management Studio.

I need help creating a quick FOR INSERT trigger for a table called dbo.Employees using the popular Northwind sample database. The trigger should default the 'Region' column in dbo.Employees to 'WA' if no region was inserted.

And if the region inserted was 'WA', then the 'Country' column should default to 'USA'. Any help would be much appreciated.

The employees table if it helps:

1

Upvotes: 0

Views: 200

Answers (1)

Evaldas Buinauskas
Evaldas Buinauskas

Reputation: 14077

Like Gordon Linoff said, this should be a constraint, but if you really needs to get this done using triggers, this will work:

CREATE TRIGGER dbo.EmployeesTrigger ON dbo.Employees
FOR INSERT
AS
BEGIN
    UPDATE E
    SET E.Country = CASE I.Country
            WHEN 'WA' THEN 'USA'
            ELSE ISNULL(I.Country, 'WA')
        END
    FROM dbo.Employees AS E
    INNER JOIN INSERTED AS I
        ON I.EmployeeID = E.EmployeeID;
END

It will update record once something has been inserted.

  • If WA was inserted as a country, it will update it to USA
  • If NULL or nothing was inserted, it will default to WA
  • On any other case, it will insert whatever was inserted, let's say Canada

Upvotes: 2

Related Questions