MSSQL 删除触发器的问题

[heng0413]

系统环境 windows xp sqlserver 2005

要求：写一个删除触发器，当子表记录被删除时，将该记录的订单号和序号以“+”连接存入 DeletedHistory表中。

注：当删除主表记录时，子表相关记录同时被删除

在线等 ，谢谢!

主表表结构

CREATE TABLE [JSERP].[SO](
[SO_SOID] [char](10) COLLATE Chinese_PRC_CI_AS NOT NULL,--订单号
[so_cjsj] [datetime] NULL DEFAULT (getdate()),
PRIMARY KEY CLUSTERED 
(
	[SO_SOID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

子表表结构

CREATE TABLE [JSERP].[SOMX](
[SOMX_SOID] [char](10) COLLATE Chinese_PRC_CI_AS NOT NULL,---订单号
[SOMX_SOXH] [float] NOT NULL,                             ---订单序号
[u_somx_whsj] [datetime] NULL,                            ---维护时间 
 CONSTRAINT [PK_jserp.somx] PRIMARY KEY NONCLUSTERED 
(
	[SOMX_SOID] ASC,
	[SOMX_SOXH] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

删除记录表表结构

CREATE TABLE [JSERP].[DeletedHistory](
[ID] [int] IDENTITY(1,1) NOT NULL,
[DeleteID] [nchar](40) COLLATE Chinese_PRC_CI_AS NOT NULL,   ---删除记录的主键，联合主键的采用“+”连接
[TableName] [nchar](20) COLLATE Chinese_PRC_CI_AS NOT NULL,  ---删除记录所在表 表名
[DeleteDate] [datetime] NOT NULL CONSTRAINT [DF_DeletedHistory_DeleteDate]  DEFAULT (CONVERT([datetime],CONVERT([char](100),getdate(),(120)),(0))),
 CONSTRAINT [PK_DeletedHistory] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

[heng0413]

[Quote=引用 5 楼 xys_777 的回复:]
SQL code
if object_id('so') is not null drop table so
go
CREATE TABLE [SO](
[SO_SOID] [char](10) COLLATE Chinese_PRC_CI_AS NOT NULL,--订单号
[so_cjsj] [datetime] NULL DEFAULT (getdate()),
PRIMARY K……
[/Quote]

问题解决 谢谢~! 结贴给分

[zheninchangjiang]

create trigger trgtest on [jserp].[somx]
for delete
as
insert into jserp.deletedhistory(deletedid,tablename,deletedate) select somx_soid+"+"+somx_soxh,'jserp.somx',get_date() from deleted

[zheninchangjiang]

get_date->getdate

[hokor]

CREATE TRIGGER [JSERP].[trgDelete]
ON [JSERP].[SOMX]
FOR DELETE 
AS 
begin
 insert into [JSERP].[DeletedHistory]([DeleteID],[TableName]) select rtrim([SOMX_SOID])+'+'+convert(varchar,[SOMX_SOXH]) [DeleteID],'[SOMX]' [TableName] from deleted
END

[hokor]

[Quote=引用 3 楼 hokor 的回复:]
SQL code
CREATE TRIGGER [JSERP].[trgDelete]
ON [JSERP].[SOMX]
FOR DELETE
AS
begin
insert into [JSERP].[DeletedHistory]([DeleteID],[TableName]) select rtrim([SOMX_SOID])+'+'+convert(varchar,[S……
[/Quote]
rtrim() 不是必须的如果不需要可以删掉。

[永生天地]

if object_id('so') is not null drop table so
go
CREATE TABLE [SO](
[SO_SOID] [char](10) COLLATE Chinese_PRC_CI_AS NOT NULL,--订单号
[so_cjsj] [datetime] NULL DEFAULT (getdate()),
PRIMARY KEY CLUSTERED 
(
    [SO_SOID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
go
if object_id('trd_so') is not null drop trigger trd_so
go
create trigger trd_so
on so
instead of delete
as
begin
	delete from [SOMX] where [SOMX_SOID] in(select [SO_SOID] from deleted)
	delete from [SO] where [SO_SOID] in(select [SO_SOID] from deleted)
	insert [DeletedHistory] select [SO_SOID],'SO',getdate() from deleted
end
go
if object_id('somx') is not null drop table somx
go
CREATE TABLE [SOMX](
[SOMX_SOID] [char](10) COLLATE Chinese_PRC_CI_AS NOT NULL,---订单号
[SOMX_SOXH] [float] NOT NULL,                             ---订单序号
[u_somx_whsj] [datetime] NULL,                            ---维护时间 
 CONSTRAINT [PK_jserp.somx] PRIMARY KEY NONCLUSTERED 
(
    [SOMX_SOID] ASC,
    [SOMX_SOXH] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
go
if object_id('trd_SOMX') is not null drop trigger trd_SOMX
go
create trigger trd_SOMX
on SOMX
for delete
as
begin
	insert [DeletedHistory] select rtrim([SOMX_SOID])+'+'+ltrim([SOMX_SOXH]),'SOMX',getdate() from deleted
end
go

if object_id('DeletedHistory') is not null drop table DeletedHistory
go
CREATE TABLE [DeletedHistory](
[ID] [int] IDENTITY(1,1) NOT NULL,
[DeleteID] [nchar](40) COLLATE Chinese_PRC_CI_AS NOT NULL,   ---删除记录的主键，联合主键的采用“+”连接
[TableName] [nchar](20) COLLATE Chinese_PRC_CI_AS NOT NULL,  ---删除记录所在表 表名
[DeleteDate] [datetime] NOT NULL CONSTRAINT [DF_DeletedHistory_DeleteDate]  DEFAULT (CONVERT([datetime],CONVERT([char](100),getdate(),(120)),(0))),
 CONSTRAINT [PK_DeletedHistory] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
go
--测试
insert so select 'A001',getdate() union select 'A002',getdate()
insert [SOMX] 
select 'A001','1',getdate() union 
select 'A001','2',getdate() union 
select 'A001','3',getdate() union 
select 'A002','1',getdate() union 
select 'A002','2',getdate() 
go
delete [SOMX] where [SOMX_SOID]='A001' and [SOMX_SOXH]=1
select * from [DeletedHistory]
go
delete [SO] where [SO_SOID]='A002' 
select * from [DeletedHistory]

/*

(2 行受影响)

(5 行受影响)

(1 行受影响)

(1 行受影响)
ID          DeleteID                                 TableName            DeleteDate
----------- ---------------------------------------- -------------------- -----------------------
1           A001+1                                   SOMX                 2010-07-28 12:38:56.623

(1 行受影响)


(2 行受影响)

(2 行受影响)

(1 行受影响)

(1 行受影响)

(1 行受影响)
ID          DeleteID                                 TableName            DeleteDate
----------- ---------------------------------------- -------------------- -----------------------
1           A001+1                                   SOMX                 2010-07-28 12:38:56.623
2           A002+2                                   SOMX                 2010-07-28 12:38:56.640
3           A002+1                                   SOMX                 2010-07-28 12:38:56.640
4           A002                                     SO                   2010-07-28 12:38:56.640

(4 行受影响)

*/

[hokor]

果然是高手，学习了。。第一次写trigger 不太会。。