asked on

Update Column when data is update.

Hi Experts,

I have a table defined as follows:

CREATE TABLE [dbo].[CallLog] (
      [CallID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
      [CallStatus] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
      [StartDate] [datetime] NULL ,
      [StopDate] [datetime] NULL
) ON [PRIMARY]

The column [CallStatus] can contain one of the values ('Open', 'On-Hold', 'Re-Opened', 'Closed').

How do I create a TRIGGER which runs only when the [Callstatus] changes to 'Closed'? The TRIGGER must update the [StopDate] to the current system Date whenever the [CallStatus] changes to 'Closed'.

Note: The TRIGGER should run only when data is UPDATE, and note when it's INSERTED or DELETED.

Guy Hengel [angelIII / a3]

CREATE TRIGGER trg_close_calllog
-- specify the table
ON CallLog
-- specify that the trigger runs only for update
FOR UPDATE
AS
-- update the table field StopDate with the current date/time
Update CallLog
set StopDate = getdate()
from Calllog l
-- join with inserted to test if the field callstatus is now 'Closed'
join inserted i
on i.callid = l.callid
and i.CallStatus = 'Closed'
-- join with delete to test if the field callstatus was not 'Closed', thus changing to 'Closed'
-- otherwise, each time the record gets updated again, StopDate would be updated again...
join deleted d
on d.callid = l.callid
and d.callid <> 'Closed'

CHeers

bmatumbura

ASKER

Thanks for the quick response angelIII,

When I created the trigger on the table, and modified any field, I got the error:

[Microsoft][ODBC SQL ...][SQL Server]Error converting data type varchar to numeric

I don't know where this error is coming from. Any ideas?

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial