Sql server database trigger operating summary 01

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: