If you read the last post on materialized views in MySQL, then you may be wondering how to create triggers in Microsoft SQL server. Microsoft SQL server has a slightly different approach, and this example will explain how to update the "LastUpdate" field for a data element (which wasn't covered in the last post).
First off, Microsoft SQL uses Transact-SQL (or T-SQL) which is an extension of the SQL standard. In T-SQL you can declare variables, write stored procedures and use conditional branching.
So why the difference? While the queries are the same, creating databases and triggers are up to the discretion of the manufacturer. In this case, creation of triggers are one of those differences.
Assume the database is named "DatabaseName" and each element has a "UniqueId" field that is unique. Now setting the LastUpdate field on each INSERT or UPDATE operations can be done as follows:
CREATE TRIGGER [dbo].[DatabaseName_InsertUpdate] ON [dbo].[DatabaseName] AFTER INSERT, UPDATE AS BEGIN DECLARE @ID nvarchar(36); -- standard UUID, 36 characters long SELECT @ID = ins.UniqueId FROM INSERTED ins; UPDATE [dbo].[DatabaseName] SET LastUpdate = getdate() WHERE UniqueId = @ID; -- getdate() gets the current date END GO
Now every time a record is updated in "DatabaseName", the LastUpdate will reflect that chage.