Raise error Function

Transact-SQL
1

I have a table called validation_checks with a field called status ( 0 or 1 ). I need help creating a script
that would check the status field for 1 and if found call the raise error function.

2

when do we check the "status" column value as 1 while inserting or updating into validation_checks table? based on DML action, we have to write corresponding trigger(for insert/update or both actions) to raise the user defined error.

below is sample trigger

create trigger test_trigger
on test2
for insert , update
as
begin
declare
@var bit
select @var = status from inserted
if @var = 1
begin
raiserror('userdefined error message',16,1)
rollback
end
end

3

TRIGGER needs to be written assuming that pseudo-tables (s.g. INSERTED) contains MULTIPLE records.

Therefore something like this would do the trick

IF EXISTS  (SELECT * FROM inserted WHERE status = 1)
begin
    raiserror('userdefined error message',16,1)
    rollback
end

If you just want to prevent any process from saving the row with Status=1 then put a CHECK CONSTRAINT on that column. If you are going to RAISERROR in a Trigger and ROLLBACK the transaction then I expect the CHECK CONSTRAINT method will work just as well - the user will get a rude, largely uninformative, message and the database will be safe :smile:

4

Thanks I will test you solution.