by Jean-Rene Roy
5. August 2011 00:42
Has a consultant I come and go in different enterprise and I meet Software Developers and DBAs. In SQL Server, there is many aspects that are useless for DBA and very useful to the developers. But most developers I meet want to stay away from T-SQL. Almost like, if you do T-SQL you are a DBA and we know how Developers love DBA’s J.
Many developers ask me these questions.
· How to update automatically a field during a T-SQL update statement?
· How can I update the current row from a trigger in T-SQL?
This is how:
-- =============================================
-- Author: Jean-Rene Roy
-- Create date: August 4th, 2011
-- Description: Will update field ModDateTime for all updates
-- =============================================
CREATE TRIGGER [dbo].[MyTable_ModDateTime]
ON [dbo].[MyTable]
AFTER UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
SET NOCOUNT ON;
Update MyTable
set ModDateTime = GetDate()
from inserted
Where MyTable.id = inserted.ID
SET NOCOUNT OFF;
END
SQL Server will populate the Row Set call inserted during the UPDATE or INSERT statement. You can access this row with the ‘’From inserted’’ clause. In the case of a Delete statement you can access the deleted row with the ‘’From deleted’’ clause. If your table has a identity field and you need to access it you can use the @@IDENTITY system function to read it from a trigger.
Subscribe