触发器的写法,忘高手指教!
现有表 receive ,结构照下面定义的,要再receive上建一触发器 TR_RECEIVE
功能是将 receive 表中插入的记录的字段 [receive_no] 作为表名;
如果没有叫[receive_no]这个表,就建立这个表。
然后将插入receive表的记录复制到[receive_no]这个表中,并将receive表中的相应记录删除
其中receive表中的数据很可能是insert into receive select * from XXX来的
谢谢!
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[receive]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[receive]
GO
CREATE TABLE [dbo].[receive] (
[receive_id] [int] IDENTITY (1, 1) NOT NULL ,
[receive_content] [nvarchar] (70) COLLATE Chinese_PRC_CI_AS NULL ,
[receive_no] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[receive_datetime] [smalldatetime] NULL
) ON [PRIMARY]
GO
CREATE TRIGGER [TR_RECEIVE] ON dbo.receive
FOR INSERT
AS
Declare @receiveno char(10)
select @receiveno=receive_no from inserted
if not exists (select * from dbo.sysobjects where id = object_id(@receiveno) and OBJECTPROPERTY(id, N'IsUserTable') = 1)
exec('CREATE TABLE [dbo].['+@receiveno+'] (
[receive_id] [int] NOT NULL ,
[receive_content] [nvarchar] (70) COLLATE Chinese_PRC_CI_AS NULL ,
[receive_no] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[receive_datetime] [smalldatetime] NULL
) ON [PRIMARY]')
GO