在等待:教器的
表和器代如下:
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表中的
明:名情的代可分


[解决办法]
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 service_empl_index = l.service_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 service_empl_index = l.service_empl_index)

[解决办法]
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