SQL 服务器触发器 - 变量表名

Question

有没有办法使用 SQL 触发器更新变量表？

我有一个名为读数的表，它使用多个不同的点数进行更新，我想在这个表上有一个触发器，它将读取这个点数，然后更新特定的表。

我有以下有效的触发器，但表名Z01我想成为一个变量，具体取决于插入的数据。这可能吗？

CREATE TRIGGER [dbo].[TRG_InsertTest] 
ON [dbo].[readings]
AFTER INSERT AS
BEGIN
INSERT INTO Z01 
SELECT * FROM INSERTED
END

Answer 1

这里有一些快速片段来测试触发器中的动态SQL。看起来有效！

-- create the tables
create table Readings
(
id      int identity,
tbl     varchar(10),
some_val    int
)
create table Z01
(
id      int identity,
some_val    int
)
create table Z02
(
id      int identity,
some_val    int
)
go
-- create the insert trigger
CREATE TRIGGER [dbo].[TRG_InsertTest] 
ON [dbo].[Readings]
AFTER INSERT AS
BEGIN
declare @sql    nvarchar(max)
select  @sql    = isnull(@sql, '')
+ N'INSERT INTO '  + quotename(tbl) + ' (some_val) VALUES (' + convert(varchar(10), some_val) + ');'
from    inserted
exec    sp_executesql @sql
END
GO
-- some testing data
insert into Readings (tbl, some_val) 
select 'Z01', 10    union all
select 'Z01', 11    union all
select 'Z02', 20
select  *
from    Readings
select  *
from    Z01
select  *
from    Z02

Answer 2

你可以这样做 -

CREATE TRIGGER [dbo].[TRG_InsertTest] 
ON [dbo].[readings]
AFTER INSERT AS
BEGIN
DECLARE @a varchar(50)
DECLARE @b int
DECLARE @c datetime
DECLARE @d varchar(20)
SET NOCOUNT ON
select  @a =  a  FROM INSERTED
select  @b=  b FROM INSERTED
select  @c =  c  FROM INSERTED
select  @d =  d  FROM INSERTED
If(@a = some condition and @b = some condition )
begin
INSERT INTO Z01 
SELECT * FROM INSERTED
end
Else If(@a = some condition and @c = some condition )
begin
INSERT INTO Z02 
SELECT * FROM INSERTED
end
Else If(@b = some condition and @d = some condition )
begin
INSERT INTO Z03
SELECT * FROM INSERTED
end
Else
begin
INSERT INTO Z04
SELECT * FROM INSERTED
end
END