Trigger

Oh, Excuse me.

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.
"

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.
"

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.

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

you mean that this statement :
update tbTest set fstatus = 'CLO', actual_date = null

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

Regards,
Sudhi.

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