根据日期更新触发器 [英] Update trigger based on dates

问题描述

我想要根据该日期的更新状态

例子

i want update status based on dates for that one

Example

create trigger emp_trigger on emp
after insert
as
begin
declare @todate date
declare @fromdate Date
declare @dt Date
Set @todate='2011-12-01'
set @fromdate='2011-12-06'
set @dt='2011-12-08'
if @dt not between  @todate and @fromdate
begin
update emp set status='expire' where empid=1
end
End

我正在编写此触发器，但是我无法更新此检查信息

I am writting this trigger, but i am not able to update this check it

推荐答案

Hello

这个触发器有点奇怪，因为它只会更新empid = 1的一行！因此，如果该行不存在，则不会执行任何操作.

如果要更新当前要插入的行，则应从触发器语句中删除where子句.

Hello

This trigger is a bit weird as it will only ever update one row where empid=1! so if the row does not exist it will do nothing.

If you want to update the row you are currently inserting you should remove the where clause from the trigger statement.

create trigger emp_trigger 
on emp
after insert
as
begin
    declare @todate date
    declare @fromdate Date
    declare @dt Date
    Set @todate='2011-12-01'
    set @fromdate='2011-12-06'
    set @dt='2011-12-08'
    if @dt not between  @todate and @fromdate
    begin
        update emp set status='expire' 
    end
End

Valery.

Valery.

在触发器中，您仅更新了ID为1的行！那其他条件呢?

您可以从inserted 和deleted 表访问插入或删除的行.
所以请看这里:
http://msdn.microsoft.com/en-us/library/aa214435％28v = sql.80％29.aspx [ ^ ]

另一件事是，您不希望触发器中有固定的日期，如果是这样，则不必具有触发器！

因此您的触发器可以像这样:

In your trigger you only updated the row with ID of 1 ! So what about other conditions ?

You can access inserted or deleted rows from the inserted and deleted tables.
So have look at here :
http://msdn.microsoft.com/en-us/library/aa214435%28v=sql.80%29.aspx[^]

Another thing is that you don''t want constant dates in your trigger, if so it''s not necessary to have a trigger !

So your trigger can be like this :

create trigger emp_trigger on emp
after insert
as
begin
declare @todate date
declare @fromdate Date
declare @dt Date
Set @todate='2011-12-01'
set @fromdate='2011-12-06'


update emp set status= 'expired'      where Id in (select ID from inserted i where not i.dt between @fromdate and @todate )
update emp set status= 'not expired'  where Id in (select ID from inserted i where     i.dt between @fromdate and @todate )	

End

在此触发器中，假定emp 表中有一个dt 字段，并且它决定该字段中status 字段中应该包含的内容.

希望对您有所帮助.

In this trigger its assumed that there is a dt field in your emp table and it decides on that field that what should be in status field.

Hope it helps.

这篇关于根据日期更新触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！