question

KristnaJuchov-7060 avatar image
0 Votes"
KristnaJuchov-7060 asked LiHongMSFT-3908 commented

rating trigger

I need to create sql trigger if rating is more than 5 throw error


CREATE TRIGGER RATING_VALUE
BEFORE INSERT ON HOC_Reviews FOR EACH ROW
BEGIN
IF new.rating > 5 THEN
RAISERROR( "You can rate only from 1 to 5");
END IF;
END;/

sql-server-generalsql-server-transact-sqlsql-server-analysis-services
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @KristnaJuchov-7060
Is there any update about this issue?If all of the answers are not working or helpful, please share with us your confusions.
And don't forget to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.
Thanks for your understanding!

Best regards,
LiHong

0 Votes 0 ·
ErlandSommarskog avatar image
2 Votes"
ErlandSommarskog answered

That syntax looks like a mix of SQL Server and Oracle.

Anyway, that particular restriction is best implemented as a CHECK constraint:

ALTER TABLE HOC_reviews ADD ch_tbl_rating CHECK (rating BETWEEN 1 AND 5)


Key points for triggers in SQL Server:

  • There are no BEFORE triggers. There are INSTEAD OF and AFTER triggers. The latter are easier to implement.

  • A trigger once per statement; there is no per-row trigger.

  • In a trigger for INSERT, you have access to the virtual table inserted, holds the newly inserted rows. The schema is the same as parent table for the trigger.

  • Likewise, in trigger for DELETE, you have the virtual table deleted.

  • And in a trigger for UPDATE, inserted holds the afterimage of the rows, and deleted has the beforeimage.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

LiHongMSFT-3908 avatar image
1 Vote"
LiHongMSFT-3908 answered

Hi @KristnaJuchov-7060
As Erland said, there is no 'Before' Trigger in SQL Sever. However, in this issue, you can use FOR/AFTER triggers. Refer to this article for more details: CREATE TRIGGER (Transact-SQL)
Check this example:

 IF OBJECT_ID('RATING_VALUE')IS NOT NULL
 DROP TRIGGER RATING_VALUE
 GO
    
 CREATE TRIGGER RATING_VALUE ON HOC_Reviews
 AFTER INSERT
 AS
 IF EXISTS(select 1 from inserted where RATING_VALUE not between 1 and 5) 
 BEGIN  
    RAISERROR('You can rate only from 1 to 5',10,1)
    ROLLBACK TRANSACTION;
    RETURN
 END; 

In addition,you can also implement this: if rating value > 5, then set it to 5.
Like this:

 IF OBJECT_ID('RATING_VALUE')IS NOT NULL
 DROP TRIGGER RATING_VALUE
 GO
    
 CREATE TRIGGER RATING_VALUE ON HOC_Reviews
 AFTER INSERT
 AS
 BEGIN  
    UPDATE HOC_Reviews SET RATING_VALUE = 5 WHERE RATING_VALUE > 5
 END; 

Best regards,
LiHong


If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.