1.Creating a trigger:
Syntax:
CREATE TRIGGER trigger_name ON {table_name|view_name}
[WITH ENCRYPTION]
{ { {FOR|AFTER|INSTEAD OF} { [DELETE][,][INSERT][,][UPDATE] } [NOT FOR REPLICATION]
AS
[{IF UPDATE(column)[{AND|OR}UPDATE(column)]
[...n]
|IF(COLUMNS_UPDATED(){bitwise_operator}updated_bitmask){comparison_operator}column_bitmask[...n]}]
sql_statement[...n]
}
}
CREATE TRIGGER C_insert ON hp_Camera FOR INSERT
AS
DECLARE @area_val NVARCHAR(50),@latitude_val FLOAT
SELECT @area_val=Area,@latitude_val=Latitude FROM INSERTED
IF(LEFT(@area_val,6)!=N'黄埔区')
BEGIN
ROLLBACK TRANSACTION
RAISERROR('输入的区信息不是黄埔区',16,1,@area_val)
END
IF(@latitude_val=0.0)
BEGIN
ROLLBACK TRANSACTION
RAISERROR('纬度值不能为零',16,1,30)
END
2.When you inserted different data to hp_camera table,the above C_insert trigger will be triggered:
a.With the '黄埔区' invalid data,got the following figure:
b.With the 0 invalid data,got the following figure:
c.With the correct data,got the following figure: