CODEWITHSUNDEEP
sqlsql-servertriggers
dimka87il
dimka87il

Reputation: 95

Triggers - with SQL Server

How can I create a trigger that runs before update or insert and checks if the new row has a specific criteria?

Let's say column A has value bigger than 5

thanks.

Upvotes: 1

Views: 229

Answers (2)

Oleksandr Fedorenko
Oleksandr Fedorenko

Reputation: 16904

You can use INSTEAD OF trigger

Simple example:

CREATE TRIGGER iu_trigger ON [dbo].[SalesHistory]
INSTEAD OF INSERT, UPDATE
AS
BEGIN
  IF EXISTS (
             SELECT 1
             FROM inserted
             WHERE A > 5
             )
  BEGIN
    PRINT 'A > 5'     
  END
  ELSE
  BEGIN
    PRINT 'A <= 5'
  END            
END

Upvotes: 1

MarcinJuraszek
MarcinJuraszek

Reputation: 125650

let's say column A has value bigger than 5

It should be done using CHECK constraint on a table column instead of TRIGGER, e.g.:

 CREATE TABLE [dbo].[SalesHistory](
      (...)
      [A] [int] NULL CHECK (A > 5)
)

Upvotes: 3

Related Questions