前面以后触发器为例介绍了触发器的基本内容,下面再介绍一下前触发器的不同之处。要创建一个前触发器必须用Instead Of 显式声明,如下面的例子: create Trigger TrackCustomerUpdates On AppDta.dbo.Customer Instead Of Update As Insert Into AppDta.dbo.CustUpdLog (CustId, Action, UpdUser, UpdDateTime) Select CustId, ‘Update’, Current_User, Current_TimeStamp From inserted 与后触发器不同的是:前触发器既可以在表又可以在视图上创建,但一条语句只能创建一个前触发器,因此,前触发器不存在激活顺序问题
触发器应用举例:从当前数据库服务器的Shop表Insert操作同步到另一台服务器的Shop。
CREATE TRIGGER Trigger_SynShopForInsert1 ON dbo.Shop FOR INSERT AS insert into OtherServer.dbo.shop ( lngShopID,strShopCode,strName,strShopName,strDescription,lngIndex ) select lngShopID,strShopCode,strName,strShopName,strDescription,lngIndex from shop where lngShopID in (select lngshopid from inserted)
或者:
CREATE TRIGGER Trigger_SynShopForInsert2 ON dbo.Shop FOR INSERT AS insert into OtherServer.dbo.shop ( lngShopID,strShopCode,strName,strShopName,strDescription,lngIndex ) select lngShopID,strShopCode,strName,strShopName,strDescription,lngIndex from inserted