如何在Sql Server 2012中创建并执行触发器内的临时表
我需要在sql server中使用特定的表名创建并执行触发器内的临时表。当我编写sql脚本来创建它创建的临时表时,不同的表名使用了十六进制代码。请尽早给出解决方案。我为你提供了一个删除sql脚本的测试触发器:
ALTER TRIGGER [dbo]。[trdeleteOperation]
[dbo] 。[人]
删除后AS
开始
- **************** ****** #temp table ************
IF OBJECT_ID(N'#tmp')IS NULL
BEGIN
BEGIN尝试
创建表#cmmsGlobal
(
AuditUser varchar(36 )not null default'',
AuditIP varchar(39)not null default'',
AuditNotes varchar(4000)not null default'',
AuditClientUTC datetime not null默认getutcdate()
)
INSERT INTO #tmp(AuditUser,AuditIP,AuditNotes,AuditClientUTC)
值('','','',getutcdate())
结束尝试
BEGIN CATCH
SELECT ERROR_NUMBER()AS ErrorNumber;
END CATCH
结束
- ************** ******** ********************
END
触发器触发后,临时表名称会有一些十六进制代码(比如#B021 ......)而不是#tmp。请建议并给我一个这个解决方案或上面的修正示例。
I need to create and execute a temp table inside trigger with a specific table name in sql server. when I write the sql script to crate the temp table it's created but something different table name used hex code. please give the solution at your earliest. I am providing you a test trigger for delete sql script below:
ALTER TRIGGER [dbo].[trdeleteOperation]
on [dbo].[Person]
after delete AS
begin
--********************** #temp table ************
IF OBJECT_ID (N'#tmp') IS NULL
BEGIN
BEGIN TRY
CREATE TABLE #cmmsGlobal
(
AuditUser varchar(36) not null default '',
AuditIP varchar(39) not null default '',
AuditNotes varchar(4000) not null default '',
AuditClientUTC datetime not null default getutcdate()
)
INSERT INTO #tmp(AuditUser,AuditIP,AuditNotes,AuditClientUTC)
values('','','',getutcdate())
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber;
END CATCH
END
--******************************************
END
After trigger fire the temp table name will some hex code (like #B021...) not #tmp. Please suggest and give me an example for this solution or correction the above.
示例触发器的第一个问题是你试图插入到一个表中不存在 - 你创建#cmmsGlobal
然后尝试使用#tmp
一旦触发器完成,那么由它创建的任何临时表都超出了范围,并且启动触发器的会话无法访问 - 请查看创建 [ ^ ]
以这种方式使用临时表是没有意义的。
在主数据库上创建审计表,或者在tempdb上创建实际表
例如
First problem with your sample trigger is that you are trying to insert into a table that doesn't exist - you create#cmmsGlobal
but then try to use#tmp
Once the trigger has completed then any temporary tables created by it go out of scope and are not accessible to the session that instigated the trigger - check out the documentation for CREATE[^]
It doesn't really make sense to use a temporary table in this way.
Either create an audit table on your main database, or you could create an actual table ontempdb
e.g.
CREATE TABLE [tempdb].[dbo].tmp
...