How to create conditional SQL insert using trigger

Question

I'm new to SQL and working in SQL Server. There is a table called Employee with columns name, ssn, salary etc. I am trying to write a trigger that checks salary is higher than $20000 when inserting rows.

If it's higher, then do insert, else print 'Salary must be higher'. I have done some research but couldn't find solution.

Here is what I'm thinking, but this it is not working for sure.

create trigger lowSalary 
on EMPLOYEE
INSTEAD OF INSERT
AS
BEGIN 
    SET NOCOUNT ON
        INSERT INTO EMPLOYEE

        IF salary < 20000 THEN
            print 'salary must be > 20000'
END

Additionally, I don't really understand this instead of insert. I found someone used this

INSTEAD OF INSERT
AS
BEGIN 
    SET NOCOUNT ON

Why are you trying to do this through printing messages out of a trigger rather than adding a check constraint? — Martin Smith
– Martin Smith, Commented Oct 16, 2016 at 18:53

Scott Newson · Accepted Answer · 2016-10-16 19:12:28Z

1

Building on @Martin Smith's comment:

Have a look at this page on Check Constraints: http://www.w3schools.com/sql/sql_check.asp

In your case, you could create a constraint like:

CONSTRAINT chk_Salary CHECK (salary > 20000)

answered Oct 16, 2016 at 19:12

Scott Newson

2,8352 gold badges25 silver badges26 bronze badges

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

1 Comment

B.B Over a year ago

well the thing is it must be trigger.

Collectives™ on Stack Overflow

How to create conditional SQL insert using trigger

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related