TheEsisia TheEsisia - 5 days ago 6
SQL Question

How to create a Trigger that is active only during a specified time period and at the end it drops itself?

I want to prevent users from modifying my table (tblActor) from Time_1 to Time_2. I have two further restrictions:


  1. I want the trigger to be dropped automatically after the ending time.

  2. I do not want to use Job Agent.



The last solution I came up with is to create a stored procedure (sp_dropMyTrigger) that will get called from the trigger and will drop it after the intended time. But I am looking for a better solution, one that doesn't require a SP.

create proc sp_dropMyTrigger
AS
drop trigger MyTrig;
go;

create trigger MyTrig ON tblActor
Instead of update, insert
AS
IF (GETDATE() > '2016-12-05 08:30:00.000' and GETDATE()<'2016-12-06 11:45:00.000')
Begin
print 'At this time, you cannot insert of update table tblActor';
return;
End
ELSE IF (GETDATE() < '2016-12-05 08:30:00.000')
Begin
IF ((select ActorID from deleted) = NULL)
insert into tblActor
select * from deleted;
else
Begin
update tblActor
set tblActor.ActorName = deleted.ActorName,
tblActor.ActorDOB = deleted.ActorDOB,
tblActor.ActorGender = deleted.ActorGender
from deleted
where tblActor.ActorID= deleted.ActorID
end
End
ELSE
exec sp_dropMyTrigger;

Answer

Just drop it from within itself.
The catch is that you need at least one event to occur after the end time in order to activate the trigger.


Demo

create table t (i int)

Go

create trigger t_trg ON t
Instead of insert
AS
begin
    insert into t (i) select -i from inserted
    drop trigger t_trg
end

insert into t(i) values (1)    
select * from t

-1

delete t    
insert into t(i) values (1)
select * from t

1

Comments