CODEWITHSUNDEEP
sql-servert-sqlif-statementdatabase-trigger
泉泉 占柱
泉泉 占柱

Reputation: 133

Use if/else statement without else branch

I don't know how to use if else in this case. When score > 10, stop insert. Else continue insert as normally. But what is the syntax to do that?

CREATE TRIGGER invalidScore ON dbo.dbo_score
AFTER INSERT
AS
DECLARE @score DECIMAL;
SET @score = (SELECT s.score FROM Inserted s);
IF(@score > 10)
BEGIN
  RETURN 'score must be less than 10'
  ROLLBACK TRAN
END
ELSE
BEGIN
END

Upvotes: 0

Views: 1355

Answers (3)

NK0709
NK0709

Reputation: 66

'Else' is an option section you can remove this and use it,but i may like you to consider using check constraints for scenarios like this rather than adding a trigger check on score column

e.g. CREATE TABLE dbo.dbo_score(

Score int CHECK (score < 10)

);

A CHECK constraint is faster, simpler, more portable, needs less code and is less error prone

Upvotes: 0

Dale K
Dale K

Reputation: 27202

There are 3 things you need to change for this trigger to work:

  1. Remove the else section - its optional.
  2. Handle the fact that Inserted may have multiple rows.
  3. Throw the error rather than using the return statement so you can handle it in the client. And throw it after rolling back the transaction in progress.

Corrected trigger follows:

create trigger invalidScore on dbo.dbo_score
after insert
as
begin
  if exists (select 1 from Inserted S where S.Score > 10) begin
    rollback tran;
    throw 51000, 'score must be less than 10', 1;
  end
end

Upvotes: 1

Ed Bangga
Ed Bangga

Reputation: 13006

First, creating these types of sql objects should use begin.. end blocks. Second is,you can ignore the else statement.

CREATE TRIGGER invalidScore ON dbo.dbo_score
AFTER INSERT
AS
BEGIN
    DECLARE @score DECIMAL;
    SET @score = (SELECT s.score FROM Inserted s);
    IF(@score > 10)
        BEGIN
            RETURN 'score must be less than 10'
            ROLLBACK TRAN
        END
END

Upvotes: 1

Related Questions