临时表上的触发器和事务 [英] trigger and transactions on temporary tables

问题描述

我们可以在临时表上创建触发器和事务吗?

can we create trigger and transactions on temporary tables?

随后用户将插入数据时，如果提交了该数据，则将触发触发器，并且该数据将从临时表移至实际表中.

when user will insert data then , if it is committed then the trigger would be fired , and that data would go from the temporary table into the actual tables.

当SQL服务停止或服务器关闭时，临时表将被自动删除.

and when the SQL service would stop, or the server would be shutdown, then the temporary tables would be deleted automatically.

还是我应该使用另一个实际表，首先将数据插入其中，然后如果提交了该触发器，则将触发触发器并将数据发送到主表，然后执行截断查询从接口表中删除数据，从而删除重复的数据.

or shall i use an another actual table , in which first the data would be inserted and then if it is committed then the trigger would be fired and the data would be sent to the main tables and then i would execute a truncate query to remove data from the interface table, hence removing the duplicate data.

推荐答案

我认为您不了解触发器-触发器触发与它们相关的语句相关联，而不是在事务提交时.两个脚本:

I don't think you understand triggers - trigger firing is associated with the statement that they're related to, rather than when the transaction commits. Two scripts:

脚本1:

create table T1 (
    ID int not null,
    Val1 varchar(10) not null
)
go
create table T2 (
    ID int not null,
    Val2 varchar(10) not null
)
go
create trigger T_T1_I
on T1
after insert
as
    insert into T2 (ID,Val2) select ID,Val1 from inserted
go
begin transaction
insert into T1 (ID,Val1)
select 10,'abc'
go
RAISERROR('Run script 2 now',10,1) WITH NOWAIT
WAITFOR DELAY '00:01:00'
go
commit

脚本2:

select * from T2 with (nolock)

打开到同一数据库的两个连接，在每个连接中放置一个脚本.运行脚本1.当显示消息立即运行脚本2"时，切换到另一个连接.您将看到，即使触发器已插入数据，您仍可以从T2中选择未提交的数据.(这还意味着脚本1将在T2上保留适当的锁，直到触发器提交为止.)

Open two connections to the same DB, put one script in each connection. Run script 1. When it displays the message "Run script 2 now", switch to the other connection. You'll see that you're able to select uncommitted data from T2, even though that data is inserted by the trigger. (This also implies that appropriate locks are being held on T2 by script 1 until the trigger commits).

因为这意味着您所要的只是将其插入基表中并保持事务打开，所以您可以做到这一点.

Since this implies that the equivalent of what you're asking for is to just insert into the base table and hold your transaction open, you can do that.

如果要向用户隐藏表格的实际形状，请创建一个 view 并在其上编写触发器以更新基本表格.但是，如上所述，一旦您对视图执行了DML操作，触发器便会触发，并且您将对基表持有锁.根据其他连接的事务隔离级别，它们可能会看到您的更改，或者被阻止直到事务提交.

If you want to hide the actual shape of the table from users, create a view and write triggers on that to update the base tables. As stated above though, as soon as you've performed a DML operation against the view, the triggers will have fired, and you'll be holding locks against the base table. Depending on the transaction isolation level of other connections, they may see your changes, or be blocked until the transaction commits.

这篇关于临时表上的触发器和事务的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！