检查此编码Status Colum not active自动更新以插入时间 [英] Check this coding Status Colum not active automatically update for inserting time

问题描述

表

Table

USE [demo]
GO

/****** Object:  Table [dbo].[cus]    Script Date: 10/19/2013 18:55:12 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[cus](
    [customername] [varchar](max) NULL,
    [address] [varchar](max) NULL,
    [Startdate] [date] NULL,
    [enddate] [date] NULL,
    [status] [varchar](8) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

//////////////////////////////////////////

SP



USE [demo]

GO

/ ******对象：触发器[dbo]。[trigoninsert] S. cript Date：10/19/2013 17:08:30 ****** /

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER触发器[dbo]。[trigoninsert] [dbo]。[cus]

for

insert

as

声明@customername varchar（max）;

声明@address varchar（max）;

声明@Startdate日期;

声明@enddate日期;

声明@status varchar（6）;

select @ customername = customername from插入;

从插入中选择@ address =地址;

选择@ Startdate =从插入开始;

从插入中选择@ enddate = enddate;

if（@ enddate< getdate（））>

begin

set @ status ='InActive';

回滚;

打印'值不能插入，因为结束日期小于当前日期';

结束

开始



update cus

set status = @ status where customername = @ customername;
结束





但状态栏未更新根据enddate

//////////////////////////////////////////
SP

USE [demo]
GO
/****** Object: Trigger [dbo].[trigoninsert] Script Date: 10/19/2013 17:08:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER trigger [dbo].[trigoninsert] on [dbo].[cus]
for
insert
as
declare @customername varchar(max);
declare @address varchar(max);
declare @Startdate date;
declare @enddate date;
declare @status varchar(6);
select @customername=customername from inserted;
select @address=address from inserted;
select @Startdate=Startdate from inserted;
select @enddate=enddate from inserted;
if(@enddate<getdate())>
begin
set @status='InActive';
rollback;
print 'the Value Cannot be inserted as the enddate is less than the current date';
end
begin

update cus
set status=@status where customername=@customername;
end


But Status Column Not Update for According to enddate

推荐答案

嗨克里希纳，

你已经在你的触发器中回滚插入！那么你要更新哪个数据记录？

你在更新前删除了记录！

而不是更新语句再次在你的触发器中使用insert语句，但是设置嵌套触发器服务器选项为0以防止嵌套触发器，

查看mor信息的链接：

http://technet.microsoft.com/en-us/library/ms190739%28v=sql.105%29.aspx [ ^ ]

http：// msdn。 microsoft.com/en-us/library/ms181299%28v=sql.100%29.aspx [ ^ ]

Hi Krishna,
you've rolled back insertion in your trigger! then which record of data do you want to update?
you've removed the record before update!
Instead of update statement use insert statement in your trigger again,but set the nested triggers server option to 0 to prevent nested trigger,
look at links for mor information:
http://technet.microsoft.com/en-us/library/ms190739%28v=sql.105%29.aspx[^]
http://msdn.microsoft.com/en-us/library/ms181299%28v=sql.100%29.aspx[^]

这篇关于检查此编码Status Colum not active自动更新以插入时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！