这是个人总结高人的程序写的~
请问哪位高人能帮我指导,怎样编写个存储过程来同步,本人在做毕业设计,希望
各位大哥的帮忙,也希望我这点程序能让别人学多点的知识
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