Trigger

I have a table with 3 columns:
Estimate_Date, Actual_Date, Status

I would like to copy the estimate_Date to the actual_Date when the value of status changes from 'OPN' to 'CLO' (both by insert or update) and Actual_Date is NULL
thefeathermanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chigrikCommented:
Try this (I checked it on my home computer):

CREATE TABLE dbo.tbTest (
      Estimate_Date datetime NULL ,
      Actual_Date datetime NULL ,
      fStatus char (3) NULL ,
      ID int IDENTITY (1, 1) NOT NULL ,
      CONSTRAINT PK_tbTest PRIMARY KEY  CLUSTERED
      (
            ID
      )
)
GO

CREATE TRIGGER tbTestTrig ON tbTest
FOR INSERT,UPDATE
AS
/*
I would like to copy the estimate_Date to the actual_Date when
the value of status changes from 'OPN' to 'CLO' (both by insert
or update) and Actual_Date is NULL
*/
  update tbTest set tbTest.actual_Date = tbTest.estimate_Date
   from tbTest, inserted where
  (tbTest.Actual_Date is NULL) and (tbTest.fStatus = 'OPN')
   and (inserted.id = tbTest.id)
-- and (inserted.fStatus = 'CLO')
GO

insert into tbTest (Estimate_date, Actual_date, fStatus)
  values ('05/05/2000', null, 'OPN')
insert into tbTest (Estimate_date, Actual_date, fStatus)
  values ('06/06/2000', null, 'OPN')
insert into tbTest (Estimate_date, Actual_date, fStatus)
  values ('07/07/2000', null, 'OPN')
GO

select * from tbTest
GO

update tbTest set fStatus = 'CLO'
GO

select * from tbTest
GO
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
chigrikCommented:
Oh, Excuse me.
0
chigrikCommented:
How I could write such? Excuse me again.
It's from Books online:
"
A trigger does not call itself in response to a second update to the same table within the trigger. For example, if an update trigger on one column of a table results in an update to another column, the update trigger is activated only once.
"
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

chigrikCommented:
How I could write such? Excuse me again.
It's from Books online:
"
A trigger does not call itself in response to a second update to the same table within the trigger. For example, if an update trigger on one column of a table results in an update to another column, the update trigger is activated only once.
"
0
sudhi022299Commented:
This is modification of the trigger code which chiqrik posted.

CREATE TABLE dbo.tbTest (
Estimate_Date datetime NULL ,
Actual_Date datetime NULL ,
fStatus char (3) NULL ,
ID int IDENTITY (1, 1) NOT NULL ,
CONSTRAINT PK_tbTest PRIMARY KEY  CLUSTERED
(
ID
)
)
GO
drop trigger tbtesttrig
CREATE TRIGGER tbTestTrig ON tbTest
FOR INSERT,UPDATE
AS
/*
I would like to copy the estimate_Date to the actual_Date when
the value of status changes from 'OPN' to 'CLO' (both by insert
or update) and Actual_Date is NULL
*/
  update tbTest set tbTest.actual_Date = tbTest.estimate_Date
   from tbTest, inserted where
  (tbTest.Actual_Date is NULL) and (tbTest.fStatus = 'OPN')
   and (inserted.id = tbTest.id)
-- and (inserted.fStatus = 'CLO')
-- the following 2 if blocks will take care of even updation and insertion.
  if update(fstatus)
  begin
   update tbtest set tbtest.actual_date = tbtest.estimate_date
    from tbTest, deleted where ( deleted.fstatus='CLO')
  end
  if update(actual_date)
   begin
    update tbtest set tbtest.actual_date = tbtest.estimate_date
    from tbTest, deleted where  deleted.actual_date is null
  end
GO

insert into tbTest (Estimate_date, Actual_date, fStatus)
  values ('05/05/2000', null, 'OPN')
insert into tbTest (Estimate_date, Actual_date, fStatus)
  values ('06/06/2000', null, 'OPN')
insert into tbTest (Estimate_date, Actual_date, fStatus)
  values ('07/07/2000', null, 'OPN')
GO
update tbtest set fstatus='CLO', actual_date = null
select * from tbTest
GO

update tbTest set actual_date = null
GO

select * from tbTest
GO

Regards,
Sudhi.
0
chigrikCommented:
Sudhi,
I am sorry, but your example not worked for me (I checked it) and it will not work, because


A trigger does not call itself in response to a second update to the same table within the trigger. For example, if an update trigger on one column of a table results in an update to another column, the update trigger is activated only once.
"

thefeatherman,

You may do the following:

1. Update this on clients side
2. Make this with one update
3. Make another table and trigger on it (I did not recommend it to use)

Something like this:

drop table tbTest1
GO

drop table tbTest2
GO

CREATE TABLE dbo.tbTest1 (
Estimate_Date datetime NULL ,
Actual_Date datetime NULL ,
fStatus char (3) NULL ,
ID int IDENTITY (1, 1) NOT NULL ,
CONSTRAINT PK_tbTest1 PRIMARY KEY  CLUSTERED
  (
    ID
  )
)
GO

CREATE TABLE dbo.tbTest2 (
Estimate_Date datetime NULL ,
Actual_Date datetime NULL ,
fStatus char (3) NULL ,
ID int IDENTITY (1, 1) NOT NULL ,
CONSTRAINT PK_tbTest2 PRIMARY KEY  CLUSTERED
  (
   ID
  )
)
GO

CREATE TRIGGER tbTest1TrigIns ON tbTest1
FOR INSERT
AS
  insert into tbTest2 (Estimate_date, Actual_date, fStatus)
    select Estimate_date, Actual_date, fStatus from inserted
GO

insert into tbTest1 (Estimate_date, Actual_date, fStatus)
  values ('05/05/2000', null, 'OPN')
insert into tbTest1 (Estimate_date, Actual_date, fStatus)
  values ('06/06/2000', null, 'OPN')
GO

CREATE TRIGGER tbTest1TrigUpd ON tbTest1
FOR UPDATE
AS
if (select count(*) from tbTest2, inserted
  where tbTest2.fStatus = 'OPN' and tbTest2.ID = inserted.ID) >0
  update tbTest2 set tbTest2.fStatus = 'CLO' from tbTest2, inserted where
  (tbTest2.Actual_Date is NULL) and (tbTest2.fStatus = 'OPN')
   and (inserted.id = tbTest2.id) and (inserted.fStatus = 'CLO')
GO

CREATE TRIGGER tbTest2TrigUpd ON tbTest2
FOR UPDATE
AS
  update tbTest1 set tbTest1.actual_Date = tbTest1.estimate_Date
   from tbTest1, inserted where
  (tbTest1.Actual_Date is NULL) and (tbTest1.fStatus = 'CLO')
   and (inserted.id = tbTest1.id) and (inserted.fStatus = 'CLO')
GO

select * from tbTest1
GO

update tbTest1 set fStatus = 'CLO'
GO

select * from tbTest1
GO
0
sudhi022299Commented:
you mean that this statement :
update tbTest set fstatus = 'CLO', actual_date = null

0
kiran_singhCommented:
chqrik:
yes it does work on my laptop.
What statement did you try which didn't work ?

Regards,
Sudhi.
0
chigrikCommented:
When I run your example I received the following:

"
Msg 111, Level 15, State 2
 CREATE TRIGGER must be the first command in a query batch.
Msg 140, Level 15, State 1
Can only use IF UPDATE within a CREATE TRIGGER.
Msg 140, Level 15, State 1
Can only use IF UPDATE within a CREATE TRIGGER.

(1 row(s) affected)


(1 row(s) affected)


(1 row(s) affected)


(3 row(s) affected)

Estimate_Date               Actual_Date                 fStatus ID          
--------------------------- --------------------------- ------- -----------
May 5 2000 12:00AM          (null)                      CLO     1          
Jun 6 2000 12:00AM          (null)                      CLO     2          
Jul 7 2000 12:00AM          (null)                      CLO     3          

(3 row(s) affected)


(3 row(s) affected)

Estimate_Date               Actual_Date                 fStatus ID          
--------------------------- --------------------------- ------- -----------
May 5 2000 12:00AM          (null)                      CLO     1          
Jun 6 2000 12:00AM          (null)                      CLO     2          
Jul 7 2000 12:00AM          (null)                      CLO     3          

(3 row(s) affected)


"

But I was wrong that it is not possible. It's my test final example:

drop table tbTest
go

CREATE TABLE dbo.tbTest (
Estimate_Date datetime NULL ,
Actual_Date datetime NULL ,
fStatus char (3) NULL ,
ID int IDENTITY (1, 1) NOT NULL ,
CONSTRAINT PK_tbTest PRIMARY KEY  CLUSTERED
(
ID
)
)
GO

insert into tbTest (Estimate_date, Actual_date, fStatus)
  values ('05/05/2000', null, 'OPN')
insert into tbTest (Estimate_date, Actual_date, fStatus)
  values ('06/06/2000', null, 'OPN')
insert into tbTest (Estimate_date, Actual_date, fStatus)
  values ('07/07/2000', null, 'OPN')
GO

CREATE TRIGGER tbTestTrig ON tbTest
FOR INSERT, UPDATE
AS
  update tbTest set tbTest.actual_Date = tbTest.estimate_Date
  from tbTest join deleted on tbTest.id = deleted.Id join inserted on deleted.id =inserted.id
  where deleted.fstatus='OPN'and inserted.fstatus='CLO' and tbTest.Actual_Date is Null
GO

select * from tbTest
GO

update tbtest set fstatus='CLO'
go

select * from tbTest
GO
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.