rating trigger

Kristína Juchová 1 Reputation point
2022-05-08T16:27:36.73+00:00

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
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,322 questions
SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,260 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,597 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Erland Sommarskog 106.5K Reputation points
    2022-05-08T17:12:55.517+00:00

    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.
    2 people found this answer helpful.
    0 comments No comments

  2. LiHong-MSFT 10,046 Reputation points
    2022-05-09T06:06:27.863+00:00

    Hi @Kristína Juchová
    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.

    1 person found this answer helpful.
    0 comments No comments