CODEWITHSUNDEEP
sqlsql-servertriggers
user1315296
user1315296

Reputation: 31

Trigger to check logic date update

Building an employee database and we want to make sure the database can't be updated incorrectly with employment end date being before the start date. We want to use a trigger to block the update, throw an error, and rollback.

I know this is wrong but this is where I am:

CREATE TRIGGER EmpLeaveWarn on Employee FOR UPDATE 
AS
    IF(select End_Date < Start_Date)
    BEGIN
        RAISERROR ('The End date must come after the Start date')
        ROLLBACK TRAN
        RETURN
    END
GO

For Microsoft SQL Server.

Thanks in advance.

Upvotes: 2

Views: 64

Answers (2)

Mureinik
Mureinik

Reputation: 311883

The easiest way to acheive this would be by using a check constraint, not a trigger:

ALTER TABLE Employee ADD CONSTRAINT Employee_Date_Check CHECK ( End_Date >= Start_Date)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270503

No need for a trigger, just use a constraint:

alter table EmpLeaveWarn
    add constraint check_end_date check (End_Date >= Start_Date);

Upvotes: 1

Related Questions