触发器的写法,忘高手指教!

loveflea 2003-07-01 02:44:34
现有表 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

...全文
94 16 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
16 条回复
切换为时间正序
请发表友善的回复…
发表回复
loveflea 2003-07-03
  • 打赏
  • 举报
回复
最后更新,支持
insert into receive ([receive_content],[receive_no],[receive_datetime]) select [receive_content],[receive_no],[receive_datetime] from tableothers

这种插入,谢谢各位!


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

ET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE TRIGGER [TR_RECEIVE] ON dbo.receive
FOR INSERT
AS
Declare @receiveid int,@receiveidmin int,@receiveidmax int, @receiveno char(10)
select @receiveidmin=min(receive_id) from inserted
select @receiveidmax=max(receive_id) from inserted
while (@receiveidmin<=@receiveidmax)
begin
select @receiveid=receive_id,@receiveno=receive_no from inserted where receive_id=@receiveidmin
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] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[receive_datetime] [smalldatetime] NOT NULL
) ON [PRIMARY]')
if exists(select * from receive where receive_id=@receiveid)
begin
exec('insert into ['+@receiveno+'] select * from receive where receive_id='+@receiveid)
delete from receive where receive_id=@receiveid
end
set @receiveidmin=@receiveidmin+1
end

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

loveflea 2003-07-02
  • 打赏
  • 举报
回复
wuyaoh(吴爻)

exec('insert '+@receiveno+ 'select * from inserted')
delete receive where receive_no = @receive_no

这两句放再我的触发器下面有错误!不能执行!


fmdsaco(老小不大)

receive只是暂时用来接收数据,然后其它表[receive_no]将对应的数据取出来处理!
fmdsaco 2003-07-02
  • 打赏
  • 举报
回复
receive只是中转站吗??
我是来学习的
帮你UP
wuyaoh 2003-07-02
  • 打赏
  • 举报
回复
还是说的有些不清楚---“将receive表中的相应记录删除“

相应是指的什么? 如果是receive_no,直接删掉就OK!

delete receive where receive_no = @receive_no
loveflea 2003-07-02
  • 打赏
  • 举报
回复
现有表 receive ,结构照下面定义的,要再receive上建一触发器 TR_RECEIVE
功能是将 receive 表中插入的记录的字段 [receive_no] 作为表名;
如果没有叫[receive_no]这个表,就建立这个表。
然后将插入receive表的记录复制到[receive_no]这个表中,并将receive表中的相应记录删除
如果有叫[receive_no]的表
将插入receive表的记录复制到[receive_no]这个表中

效率是低一些,但那个表receive是别的人写的程序从别的地方读来;由我们分类处理
所以没有办法!

tj_dns(愉快的登山者) 和 pengdali(大力 V2.0) 都只是建一个表,没有处理数据

我主要想处理这个复制和删除的东西!那个表的不是我的触发器已经建立了。

谢谢!

loveflea 2003-07-02
  • 打赏
  • 举报
回复
但是这样不支持这样的插入

insert into receive ([receive_content],[receive_no],[receive_datetime]) select [receive_content],[receive_no],[receive_datetime] from tableothers

只支持单句的insert,忘高手指教
loveflea 2003-07-02
  • 打赏
  • 举报
回复
写完了,谢谢!谁的帮忙测试一下:


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


SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Create TRIGGER [TR_RECEIVE] ON dbo.receive
FOR INSERT
AS
Declare @receiveid int, @receiveno char(10)
select @receiveid=receive_id,@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 NULL ,
[receive_datetime] [smalldatetime] NOT NULL
) ON [PRIMARY]')
if exists(select * from receive where receive_id=@receiveid)
begin
exec('insert into ['+@receiveno+'] select * from receive where receive_id='+@receiveid)
delete from receive where receive_id=@receiveid
end

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
fmdsaco 2003-07-02
  • 打赏
  • 举报
回复
没有一个分类吗

select name ,count(*) 条数 from 表 group by name
name是你要的分类共有的东东,这个帮得到你吗
fmdsaco 2003-07-02
  • 打赏
  • 举报
回复
我也正想要这种功能,我也想知道,关注
firedragoninhell 2003-07-02
  • 打赏
  • 举报
回复
我来提个异议,其实我不赞同在mis系统中使用trigger,维护起来特别不方便,不如用procedure,调用的时候是白箱,清楚明了。
loveflea 2003-07-02
  • 打赏
  • 举报
回复
举个例子,receive表,假如要插入以下记录

[receive_id] [receive_content] [receive_no] [receive_datetime]

1 土豆 1001 2003-07-02 12:57:01
2 苹果 1002 2003-07-02 12:57:02
3 香蕉 1003 2003-07-02 12:57:03
4 菠萝 1002 2003-07-02 12:57:04


对应的,就要建立

表 1001
1 土豆 1001 2003-07-02 12:57:01

表1002
2 苹果 1002 2003-07-02 12:57:02
4 菠萝 1002 2003-07-02 12:57:04

表1003
3 香蕉 1003 2003-07-02 12:57:03

并把receive表里对应的数据删掉

注意:receive表里的数据是不定时的插入的,而且还可能是连续插入
如insert into receive ([receive_content],[receive_no],[receive_datetime]) select * from tablea
leimin 2003-07-01
  • 打赏
  • 举报
回复
现有表 receive ,结构照下面定义的,要再receive上建一触发器 TR_RECEIVE
功能是将 receive 表中插入的记录的字段 [receive_no] 作为表名;
如果没有叫[receive_no]这个表,就建立这个表。
然后将插入receive表的记录复制到[receive_no]这个表中,并将receive表中的相应记录删除

楼主的逻辑好像有问题?为何不直接对RECEIVE_NO这个表写,还要通过RECEIVE,再通过TRIGGER,效率太低了。你再仔细分析一下你的逻辑,看看是否有更有效的方案。
loveflea 2003-07-01
  • 打赏
  • 举报
回复
然后将插入receive表的记录复制到[receive_no]这个表中,并将receive表中的相应记录删除

我主要想处理这个复制和删除的东西!那个表的不是我的触发器已经建立了。

谢谢!
pengdali 2003-07-01
  • 打赏
  • 举报
回复
CREATE TRIGGER [TR_RECEIVE] ON dbo.receive
INSTEAD OF INSERT
AS
Declare @receiveno char(10)
select @receiveno=receive_no from inserted
exec('select * into '+@receiveno+' from inserted')
愉快的登山者 2003-07-01
  • 打赏
  • 举报
回复
CREATE TRIGGER [TR_RECEIVE] ON dbo.receive
FOR INSERT
AS
Declare @receiveno char(10)
set @receiveno=''
while exists (select 1 from inserted where receive_no > @receiveno)
begin
select @receiveno=min(receive_no) from inserted where receive_no > @receiveno
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]')
end
GO
愉快的登山者 2003-07-01
  • 打赏
  • 举报
回复
CREATE TRIGGER [TR_RECEIVE] ON dbo.receive
FOR INSERT
AS
Declare @receiveno char(10)
set @receiveno=''
while exists (select 1 from inserted where receive_no > @receiveno)
begin
select @receiveno=min(receive_no) from inserted where receive_no > @receiveno
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]')
end
GO

27,582

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 应用实例
社区管理员
  • 应用实例社区
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧