给各位版友的一个建议.

[filebat]

建议:以后提问题时,建表,插入初始数据等SQL语句由楼主给出.
然后大家都用这种schema和数据就行了.
免得每个人都要写一次,浪费时间,
而且这种版面又不会太长.

[zaisheng09]

怎么提问亚~~
那位大哥 教教

[zaisheng09]

关于以数据库中两结构相同表的同步（用触发器）
这是个人总结高人的程序写的~

请问哪位高人能帮我指导，怎样编写个存储过程来同步，本人在做毕业设计，希望

各位大哥的帮忙，也希望我这点程序能让别人学多点的知识

if exists(select name from sysobjects where name='sale' and type='U')
drop table sale
go
if exists(select name from sysobjects where name='newsale' and type='U')
drop table newsale
go
CREATE TABLE [sale] (
[stor_id] [char] (4) COLLATE Chinese_PRC_CI_AS NULL ,
[ord_num] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[date] [datetime] NULL ,
[qty] [smallint] NULL ,
[payterms] [varchar] (12) COLLATE Chinese_PRC_CI_AS NULL ,
[title_id] [int] IDENTITY (1, 1) primary key NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [newsale] (
[stor_id] [char] (4) COLLATE Chinese_PRC_CI_AS NULL ,
[ord_num] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[date] [datetime] NULL ,
[qty] [smallint] NULL ,
[payterms] [varchar] (12) COLLATE Chinese_PRC_CI_AS NULL ,
[title_id] [int] IDENTITY (1, 1) primary key NOT NULL
) ON [PRIMARY]
GO

--我建立了两个表，我希望在对sale表进行插入、更新、删除操作时，
--将sale表中的记录同步到表newsale于是我采用了为sale表添加触发器，代码如下


if exists(select name from sysobjects where name='sale_Update' and type='TR')
drop trigger sale_Update
go
if exists(select name from sysobjects where name='sale_insert' and type='TR')
drop trigger sale_insert
go
if exists(select name from sysobjects where name='sale_delete' and type='TR')
drop trigger sale_delete
go
/*******************insert触发器***************************/
CREATE TRIGGER sale_insert ON dbo.sale
FOR INSERT
AS
set identity_insert newsale on
--delete [dbo].[newsale] where title_id in (select title_id from inserted)

insert newsale(
[stor_id],
[ord_num],
[date],
[qty] ,
[payterms],
[title_id]
)

--select * from sale where [title_id] not in (select [title_id] from newsale)
select * from inserted
--select * from inserted
--delete [dbo].[newsale] where title_id in (select title_id from inserted)

set identity_insert newsale off
go
/*******************delete触发器***************************/
CREATE TRIGGER sale_delete ON dbo.sale
FOR delete
AS
delete [dbo].[newsale] where title_id in (select title_id from deleted)
go
/*******************update触发器***************************/
CREATE TRIGGER sale_Update
ON sale
FOR UPDATE
AS
set identity_insert newsale on
IF (UPDATE(stor_id)
or UPDATE(payterms)
or UPDATE(qty)
or UPDATE(date)
or UPDATE(ord_num))
begin
--select * from inserted
delete newsale where title_id in (select title_id from inserted)
insert newsale(
[stor_id],
[ord_num],
[date],
[qty] ,
[payterms],
[title_id]
) select * from inserted

--select * from sale
end
set identity_insert newsale off
go






本文引用通告地址： http://blog.csdn.net/zaisheng09/services/trackbacks/387520.aspx
[点击此处收藏本文]
发表于 2005年06月04日 12:03 PM


没有评论。
发表评论



为什么我看不到我的问题
版主 教教