Creating triggers in Microsoft SQL

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

Note: It is assumed the the database and users have already been created.

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.