在線等待:請教觸發器的問題2
表結構和觸發器代碼如下:
CREATE TABLE [dbo].[service_empl] (
[empl_index] [char] (10) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL ,
[empl_ensuremony] [money] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[service_empl] WITH NOCHECK ADD
CONSTRAINT [service_empl] PRIMARY KEY CLUSTERED
(
[empl_index]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[admini_ensuremoney] (
[empl_index] [char] (10) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL ,
[empl_oldmon] [money] NULL ,
[empl_newmon] [money] NULL ,
[empl_date] [smalldatetime] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[admini_ensuremoney] WITH NOCHECK ADD
CONSTRAINT [PK_admin_ensuremoney] PRIMARY KEY CLUSTERED
(
[empl_index],
[empl_date]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[admini_ensuremoney] WITH NOCHECK ADD
CONSTRAINT [DF_admini_ensuremoney_empl_oldmon] DEFAULT (0) FOR [empl_oldmon],
CONSTRAINT [DF_admini_ensuremoney_empl_newmon] DEFAULT (0) FOR [empl_newmon]
GO
CREATE TRIGGER [ensuremoney_insert] ON dbo.admini_ensuremoney
FOR INSERT, UPDATE, DELETE
AS
update service_empl set service_ensuremoney=l.empl_oldmon from service_empl y , deleted l where y.service_empl_index=l.empl_index
update service_empl set service_ensuremoney=l.empl_newmon from service_empl y ,inserted l where y.service_empl_index=l.empl_index
問題:這種方式會有一種錯誤:
當我增加,修改還是刪除admini_ensuremoney表中a君的一筆記錄時,如果這筆記錄的日期不是最新的日期,則此時也會去更新service_empl表中的值.
要求:我需要不管是增加,修改還是刪除操作,只有此筆記錄的日期是最新的日期(當前操作記錄的日期> =admini_ensuremoney中此人的最大日期)時,才去更新service_empl表中的數據.且能 "同時插入多筆相同service_empl_index的資料 "和 "同時插入多筆不同service_empl_index的資料 ".
說明:名種觸發情況的代碼可分開
http://community.csdn.net/Expert/topic/5372/5372712.xml?temp=.1426966
這個貼還有50分
[解决办法]
--建立測試環境
CREATE TABLE [dbo].[service_empl] (
[service_empl_index] [char] (10) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL ,
[service_ensuremoney] [money] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[service_empl] WITH NOCHECK ADD
CONSTRAINT [PK_service_empl] PRIMARY KEY CLUSTERED
(
[service_empl_index]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[admini_ensuremoney] (
[empl_index] [char] (10) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL ,
[empl_oldmon] [money] NULL ,
[empl_newmon] [money] NULL ,
[empl_date] [smalldatetime] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[admini_ensuremoney] WITH NOCHECK ADD
CONSTRAINT [PK_admin_ensuremoney] PRIMARY KEY CLUSTERED
(
[empl_index],
[empl_date]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[admini_ensuremoney] WITH NOCHECK ADD
CONSTRAINT [DF_admini_ensuremoney_empl_oldmon] DEFAULT (0) FOR [empl_oldmon],
CONSTRAINT [DF_admini_ensuremoney_empl_newmon] DEFAULT (0) FOR [empl_newmon]
GO
--建立觸發器
CREATE TRIGGER [ensuremoney_insert] ON dbo.admini_ensuremoney
FOR INSERT, UPDATE, DELETE
AS
update service_empl
set service_ensuremoney=l.empl_oldmon
from service_empl y ,deleted l
where y.service_empl_index=l.empl_index
And l.empl_date > = (Select Max(empl_date) From admini_ensuremoney Where empl_index = l.empl_index)
And l.empl_date = (Select Max(empl_date) From deleted Where empl_index = l.empl_index)
update service_empl
set service_ensuremoney=l.empl_newmon
from service_empl y ,inserted l
where y.service_empl_index=l.empl_index
And l.empl_date > = (Select Max(empl_date) From admini_ensuremoney Where empl_index = l.empl_index)
And l.empl_date = (Select Max(empl_date) From inserted Where empl_index = l.empl_index)
GO
--插入數據
Insert service_empl Select '0000000001 ', 0
Union All Select '0000000002 ', 1
GO
--測試
Insert admini_ensuremoney Select '0000000001 ', 10, 20, '2007-02-01 '
Union All Select '0000000001 ', 20, 30, '2007-03-01 '
Union All Select '0000000001 ', 15, 35, '2007-02-11 '
Union All Select '0000000002 ', 10, 20, '2007-02-01 '
Union All Select '0000000002 ', 25, 50, '2007-03-01 '
Union All Select '0000000002 ', 50, 80, '2007-02-21 '
/*
插入六條紀錄
000000000110.000020.00002007-02-01 00:00:00
000000000115.000035.00002007-02-11 00:00:00
000000000120.000030.00002007-03-01 00:00:00
000000000210.000020.00002007-02-01 00:00:00
000000000250.000080.00002007-02-21 00:00:00
000000000225.000050.00002007-03-01 00:00:00
兩條2007-03-01的紀錄是最大的，30、50(empl_newmon)會分別更新到service_empl中
*/
Select * From service_empl
GO
Delete From admini_ensuremoney Where empl_date > = '2007-02-21 '
/*
刪除三條紀錄
000000000120.000030.00002007-03-01 00:00:00
000000000250.000080.00002007-02-21 00:00:00
000000000225.000050.00002007-03-01 00:00:00
兩條2007-03-01的紀錄是最大的，20、25(empl_oldmon)會分別更新到service_empl中
*/
Select * From service_empl
GO
Update admini_ensuremoney Set empl_newmon = empl_newmon + 10 Where empl_date <= '2007-02-21 '
/*
更新三條紀錄
000000000110.000030.00002007-02-01 00:00:00
000000000115.000045.00002007-02-11 00:00:00
000000000210.000030.00002007-02-01 00:00:00
0000000001的2007-02-11的紀錄，0000000002的2007-02-01的紀錄是最大的，
45、30(empl_oldmon)會分別更新到service_empl中
*/
Select * From service_empl
--刪除測試環境
Drop Table service_empl, admini_ensuremoney
--結果
/*
service_empl_indexservice_ensuremoney
000000000130.0000
000000000250.0000
service_empl_indexservice_ensuremoney
000000000120.0000
000000000225.0000
service_empl_indexservice_ensuremoney
000000000145.0000
000000000230.0000
*/
[解决办法]
CREATE TRIGGER [ensuremoney_insert] ON dbo.admini_ensuremoney
FOR INSERT, UPDATE, DELETE
AS
update service_empl y
set y.service_ensuremoney=l.empl_oldmon
from service_empl y
join (
select * from deleted _d
where not exists
(select 1 from deleted where empl_index = _d.empl_index and empl_date > _d.empl_date)
and _l.empl_date > =
(select max(empl_date) from admini_ensuremoney where empl_index = _l.empl_index )
) l on y.service_empl_index=l.empl_index
update service_empl y
set y.service_ensuremoney=l.empl_oldmon
from service_empl y
join (
select * from inserted _i
where not exists
(select 1 from inserted where empl_index = _d.empl_index and empl_date > _d.empl_date)
and _l.empl_date > =
(select max(empl_date) from admini_ensuremoney where empl_index = _l.empl_index )
) l on y.service_empl_index=l.empl_index
