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, anddeleted
has the beforeimage.