创建触发器,触发器是一种特殊的存储过程,在用户试图对指定的表执行指定的数据修改语句时自动执行。Microsoft® SQL Server™ 允许为任何给定的 INSERT、UPDATE 或 DELETE 语句创建多个触发器。语法 CREATE TRIGGER trigger_name ON { table | view } [ WITH ENCRYPTION ] { { { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] } [ WITH APPEND ] [ 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 ] } } 参数 trigger_name是触发器的名称。触发器名称必须符合标识符规则,并且在数据库中必须唯一。可以选择是否指定触发器所有者名称。Table | view是在其上执行触发器的表或视图,有时称为触发器表或触发器视图。可以选择是否指定表或视图的所有者名称。视图只能被 INSTEAD OF 触发器引用。
create trigger yourTrigger on yourView instead of update as .....
视图只是最终用户流览结果集。无法采用update
这个问题是这样的。在基础表run_parameter有上万条记录,sankua 存放的是run表中的id, 我要对视图中的列(up_level,up_high,down_level,down_high)值的变化进行实时监控,超过一个范围后就触发一个触发器,如300-400,达到410就触发,insert到另外一个基础表中。请问各位如何处理这样的事务。再加分相送谢谢,同时也非常感谢上面解答的朋友 表结构如下 run表:CREATE TABLE [dbo].[run_parameter] ( [DinWei_ID] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL , [LaChuZhi] [int] NULL , [DaoGao] [int] NULL , [check_date] [smalldatetime] NULL ) ON [PRIMARY] GO sankua表:CREATE TABLE [dbo].[sankua] ( [id] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL , [din_up_1] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL , --run表中id [din_up_2] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL , --同上 [din_down_1] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL , --同上 [din_down_2] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL , ) ON [PRIMARY] GO 视图: GO SET ANSI_NULLS ON GO ALTER view sankua_view(id ,up_level ,up_high ,down_level ,down_high ,check_date) as select sa.id ,(select max(LaChuZhi)-min(LaChuZhi) from run_parameter r,sankua s where r.DinWei_ID like (substring(din_up_1,1,7)+'%') and s.id=sa.id) ,(select max(DaoGao)-min(DaoGao) from run_parameter r,sankua s where r.DinWei_ID like (substring(din_up_1,1,7)+'%') and s.id=sa.id) ,(select max(LaChuZhi)-min(LaChuZhi) from run_parameter r,sankua s where r.DinWei_ID like (substring(din_down_1,1,7)+'%') and s.id=sa.id) ,(select max(DaoGao)-min(DaoGao) from run_parameter r,sankua s where r.DinWei_ID like (substring(din_down_1,1,7)+'%') and s.id=sa.id) ,getdate() from run_parameter,sankua sa group by sa.id GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
举例: create table a (a int,aa varchar(100)) create table b (b int,bb varchar(100)) go create view c as select a.*,b.* from a,b where a.a=b.b go--测试: insert c values(1,'aa',1,'bb') --失败go CREATE TRIGGER 名 on c INSTEAD OF INSERT AS BEGIN INSERT a select a,aa from inserted INSERT b select b,bb from inserted END go--测试: insert c values(1,'aa',1,'bb')
CREATE TRIGGER trigger_name
ON { table | view }
[ WITH ENCRYPTION ]
{
{ { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
[ WITH APPEND ]
[ 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 ]
}
} 参数
trigger_name是触发器的名称。触发器名称必须符合标识符规则,并且在数据库中必须唯一。可以选择是否指定触发器所有者名称。Table | view是在其上执行触发器的表或视图,有时称为触发器表或触发器视图。可以选择是否指定表或视图的所有者名称。视图只能被 INSTEAD OF 触发器引用。
我要对视图中的列(up_level,up_high,down_level,down_high)值的变化进行实时监控,超过一个范围后就触发一个触发器,如300-400,达到410就触发,insert到另外一个基础表中。请问各位如何处理这样的事务。再加分相送谢谢,同时也非常感谢上面解答的朋友
表结构如下
run表:CREATE TABLE [dbo].[run_parameter] (
[DinWei_ID] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[LaChuZhi] [int] NULL ,
[DaoGao] [int] NULL ,
[check_date] [smalldatetime] NULL
) ON [PRIMARY]
GO
sankua表:CREATE TABLE [dbo].[sankua] (
[id] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[din_up_1] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL , --run表中id
[din_up_2] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL , --同上
[din_down_1] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL , --同上
[din_down_2] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
) ON [PRIMARY]
GO
视图:
GO
SET ANSI_NULLS ON
GO
ALTER view sankua_view(id
,up_level
,up_high
,down_level
,down_high
,check_date)
as
select sa.id
,(select max(LaChuZhi)-min(LaChuZhi) from run_parameter r,sankua s where r.DinWei_ID like (substring(din_up_1,1,7)+'%') and s.id=sa.id)
,(select max(DaoGao)-min(DaoGao) from run_parameter r,sankua s where r.DinWei_ID like (substring(din_up_1,1,7)+'%') and s.id=sa.id)
,(select max(LaChuZhi)-min(LaChuZhi) from run_parameter r,sankua s where r.DinWei_ID like (substring(din_down_1,1,7)+'%') and s.id=sa.id)
,(select max(DaoGao)-min(DaoGao) from run_parameter r,sankua s where r.DinWei_ID like (substring(din_down_1,1,7)+'%') and s.id=sa.id)
,getdate()
from run_parameter,sankua sa
group by sa.id
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
create table a (a int,aa varchar(100))
create table b (b int,bb varchar(100))
go
create view c
as
select a.*,b.* from a,b where a.a=b.b
go--测试:
insert c values(1,'aa',1,'bb')
--失败go
CREATE TRIGGER 名 on c
INSTEAD OF INSERT
AS
BEGIN
INSERT a select a,aa from inserted
INSERT b select b,bb from inserted
END
go--测试:
insert c values(1,'aa',1,'bb')
insert run(DinWei_ID,LaChuZhi,DaoGao)
values('313905602B',400,6000)insert run(DinWei_ID,LaChuZhi,DaoGao)
values('313905603B',200,6500)sankua表
insert sankua(id,din_up_1,din_up_2)
values(1,'313905602B','313905603B')生成视图:
这时对列(up_level ,up_high)进行监控
如果up_level(其实就是400-200)值超过了设置的临界值,比如130时,就会自动产生触发,向另一基表中插入一个新记录。up_high同此。谢谢(大力 v3.0)关注
有没有更好的解决方案。因为(表一)实在太大,只为监控其中的一小部分记录而监控所有记录效率上有点什么。
除了视图解决,还有什么可以解决这个问题吗
支持 用触发器的朋友们!