涉及四个表:如下:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[main]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[main]
GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[maindetail]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[maindetail]
GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[mainitem]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[mainitem]
GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[maint]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[maint]
GOCREATE TABLE [dbo].[main] (
[sid] [int] NOT NULL ,
[status] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GOCREATE TABLE [dbo].[maindetail] (
[dsid] [int] NOT NULL ,
[sid] [int] NULL ,
[itemname] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[mvalue] [float] NULL ,
[humsid] [int] NULL
) ON [PRIMARY]
GOCREATE TABLE [dbo].[mainitem] (
[id] [int] NOT NULL ,
[item] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[itemname] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GOCREATE TABLE [dbo].[maint] (
[h1] [float] NULL ,
[h2] [float] NULL ,
[h3] [float] NULL ,
[humsid] [int] NOT NULL
) ON [PRIMARY]
GOALTER TABLE [dbo].[main] WITH NOCHECK ADD
CONSTRAINT [PK_main] PRIMARY KEY CLUSTERED
(
[sid]
) ON [PRIMARY]
GOALTER TABLE [dbo].[maindetail] WITH NOCHECK ADD
CONSTRAINT [PK_maindetail] PRIMARY KEY CLUSTERED
(
[dsid]
) ON [PRIMARY]
GOALTER TABLE [dbo].[mainitem] WITH NOCHECK ADD
CONSTRAINT [PK_mainitem] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
GOALTER TABLE [dbo].[maint] WITH NOCHECK ADD
CONSTRAINT [PK_maint] PRIMARY KEY CLUSTERED
(
[humsid]
) ON [PRIMARY]
GO
主表main中status更新为‘批准’时,在明细表maindetail中调出 人员编号humsid, 薪资mvalue, 薪资项名称itemname
用itemname再从表mainitem中找到对应薪资项编码item, 薪资项编码item是表maint的列名,然后利用人员编号humsid,把薪资的金额mvalue更新到表maint中列名为item的列下。我写了个触发器初本:
CREATE TRIGGER main_update ON main
FOR UPDATE
not for replication
AS
declare @state varchar(10)
declare @humsid int
declare @item varchar(20)
declare @itemname varchar(20)
declare @mvalue float
declare @sid int begin
if update(status)
begin
select @state=status from main
select @sid=sid from main
if @state='批准'
begin
select @humsid = humsid, @mvalue = mvalue,@itemname = itemname
from maindetail where sid=@sid Select @item = item from mainitem where itemname = @itemname UPDATE maint SET @item = @mvalue WHERE humsid = @humsid
end
end
end功能没能实现,请高帮忙改下!万分感谢!
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[main]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[main]
GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[maindetail]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[maindetail]
GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[mainitem]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[mainitem]
GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[maint]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[maint]
GOCREATE TABLE [dbo].[main] (
[sid] [int] NOT NULL ,
[status] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GOCREATE TABLE [dbo].[maindetail] (
[dsid] [int] NOT NULL ,
[sid] [int] NULL ,
[itemname] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[mvalue] [float] NULL ,
[humsid] [int] NULL
) ON [PRIMARY]
GOCREATE TABLE [dbo].[mainitem] (
[id] [int] NOT NULL ,
[item] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[itemname] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GOCREATE TABLE [dbo].[maint] (
[h1] [float] NULL ,
[h2] [float] NULL ,
[h3] [float] NULL ,
[humsid] [int] NOT NULL
) ON [PRIMARY]
GOALTER TABLE [dbo].[main] WITH NOCHECK ADD
CONSTRAINT [PK_main] PRIMARY KEY CLUSTERED
(
[sid]
) ON [PRIMARY]
GOALTER TABLE [dbo].[maindetail] WITH NOCHECK ADD
CONSTRAINT [PK_maindetail] PRIMARY KEY CLUSTERED
(
[dsid]
) ON [PRIMARY]
GOALTER TABLE [dbo].[mainitem] WITH NOCHECK ADD
CONSTRAINT [PK_mainitem] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
GOALTER TABLE [dbo].[maint] WITH NOCHECK ADD
CONSTRAINT [PK_maint] PRIMARY KEY CLUSTERED
(
[humsid]
) ON [PRIMARY]
GO
主表main中status更新为‘批准’时,在明细表maindetail中调出 人员编号humsid, 薪资mvalue, 薪资项名称itemname
用itemname再从表mainitem中找到对应薪资项编码item, 薪资项编码item是表maint的列名,然后利用人员编号humsid,把薪资的金额mvalue更新到表maint中列名为item的列下。我写了个触发器初本:
CREATE TRIGGER main_update ON main
FOR UPDATE
not for replication
AS
declare @state varchar(10)
declare @humsid int
declare @item varchar(20)
declare @itemname varchar(20)
declare @mvalue float
declare @sid int begin
if update(status)
begin
select @state=status from main
select @sid=sid from main
if @state='批准'
begin
select @humsid = humsid, @mvalue = mvalue,@itemname = itemname
from maindetail where sid=@sid Select @item = item from mainitem where itemname = @itemname UPDATE maint SET @item = @mvalue WHERE humsid = @humsid
end
end
end功能没能实现,请高帮忙改下!万分感谢!
--main 中
insert into main(sid, status) values(1,'新增')
go--maindetail中
insert into maindetail(dsid,sid,itemname,mvalue,humsid) values(1,1,'hh1',12,1)
go
insert into maindetail(dsid,sid,itemname,mvalue,humsid) values(2,1,'hh2',23,1)
go
insert into maindetail(dsid,sid,itemname,mvalue,humsid) values(3,1,'hh2',13,2)
go
insert into maindetail(dsid,sid,itemname,mvalue,humsid) values(4,1,'hh3',45,2)
go
insert into maindetail(dsid,sid,itemname,mvalue,humsid) values(5,1,'hh1',35,3)
go--mainitem中
insert into mainitem(id, item, itemname) values(1,'h1','hh1')
go
insert into mainitem(id, item, itemname) values(2,'h2','hh2')
go
insert into mainitem(id, item, itemname) values(3,'h3','hh3')
go--maint中
insert into mainit(humsid) values(1)
go
insert into mainit(humsid) values(2)
go
insert into mainit(humsid) values(3)
go
帮个忙,谢谢!!!
1,首先,触发器中有两个表deleted,inserted,前者用来存储update的前影像,后者用来存储更新之后的值;
2,触发器中不能查询源表,因为这时数据库无法判断你的源表是更新过还是没更新过。create TRIGGER main_update ON main
after UPDATE
AS
begin
if update(status)
begin
declare
@sql varchar(1000)
set @sql = ''
select @sql = isnull(@sql,'')+' update maint set ' + convert(varchar(10),mainitem.item) + ' = '''+
convert(varchar(10),maindetail.mvalue) + ''' where humsid = ' + convert(varchar(10),maindetail.humsid) + ''''
-- select convert(varchar(10),mainitem.item),convert(varchar(10),maindetail.mvalue),convert(varchar(10),maindetail.humsid)
from inserted,maindetail,mainitem
where inserted.status='批准' and inserted.sid = maindetail.sid and mainitem.itemname = maindetail.itemname
exec(@sql)
end
end
alter TRIGGER main_update ON main
after UPDATE
AS
begin
if update(status)
begin
declare
@sql varchar(1000)
set @sql = ''
select @sql = isnull(@sql,'')+ ' update maint set ' + convert(varchar(10),mainitem.item) + ' = '+
convert(varchar(10),maindetail.mvalue) + ' where humsid = ' + convert(varchar(10),maindetail.humsid)
from inserted,maindetail,mainitem
where inserted.status='批准' and inserted.sid = maindetail.sid and mainitem.itemname = maindetail.itemname
exec(@sql)
end
end