Ok, that I must be doing something wrong, because remember getting through this variant of the trigger and it doesn't do what it supposed to do.
Here is the table:
CREATE TABLE [dbo].[Employees](
[DateCreated] [datetime] NULL,
[DateModified] [datetime] NULL,
[Id] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [nvarchar](100) NULL,
[LastName] [nvarchar](100) NULL,
[DeptIdt] [int] NOT NULL,
CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Employees] WITH CHECK ADD CONSTRAINT [FK_Employees_Departments] FOREIGN KEY([DeptIdt])
REFERENCES [dbo].[Departments] ([Id])
GO
ALTER TABLE [dbo].[Employees] CHECK CONSTRAINT [FK_Employees_Departments]
GO
The entire data set is really simple, for exemplification purposes:
DateCreated DateModified IdFirstNameLastNameDeptIdt
2009-09-04 11:23:48.4602009-09-04 11:23:48.4608JohnDoe1
2009-09-04 11:23:48.4602009-09-04 11:23:48.4609MarryDoe2
2009-09-04 11:23:48.4602009-09-04 11:23:48.46010JohnLong Silver3
2009-09-04 11:23:48.4602009-09-04 11:23:48.46011TedKing2
The trigger is in place and looking like this:
IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[trg_UpdateDateModified]'))
DROP TRIGGER [dbo].[trg_UpdateDateModified]
GO
CREATE TRIGGER [dbo].[trg_UpdateDateModified]
ON [dbo].[Employees]
INSTEAD OF UPDATE
AS
BEGIN
UPDATE
Employees
SET
DateModified = getdate()
FROM
Employees
INNER JOIN inserted
ON Employees.ID = inserted.ID;
END
GO
I run the following queries:
select * from Employees
update Employees SET FirstName= 'Johnny' where Id=8
select * From Employees
and finally, here are the results:
BEFORE update
2009-09-04 11:23:48.4602009-09-04 11:23:48.4608JohnDoe1
2009-09-04 11:23:48.4602009-09-04 11:23:48.4609MarryDoe2
2009-09-04 11:23:48.4602009-09-04 11:23:48.46010JohnLong Silver3
2009-09-04 11:23:48.4602009-09-04 11:23:48.46011TedKing2
AFTER update:
2009-09-04 11:23:48.4602009-09-04 11:28:28.8138JohnDoe1
2009-09-04 11:23:48.4602009-09-04 11:23:48.4609MarryDoe2
2009-09-04 11:23:48.4602009-09-04 11:23:48.46010JohnLong Silver3
2009-09-04 11:23:48.4602009-09-04 11:23:48.46011TedKing2
As you can see, the DateModified is updated, but NOT the FirstName column. Am I missing something here?