In MSSQL 7.0 manual update of records are sometimes done to initialize data in a table which is normally done in a Query Analyzer window. I’ll take for example a table called StudentHistory which has new field FullName and LastNameFirstName which was added to concatenate values from two other fields FirstName and LastName. Since, the fields are added later there’s no data in it. An sql script to update the contents of the fields; will look like the following:

Update StudentHistory
set FullName = {fn CONCAT({fn RTRIM(FirstName)},{fn CONCAT(' ',{fn RTRIM(LastName)})})},
LastNameFirstName = {fn CONCAT({fn CONCAT({fn RTRIM(LastName)},', ')},{fn RTRIM(FirstName)})}
where FullName is null or LastNameFirstName is null

This is good in populating the data for the first time, but for additional records or updates in the FirstName and LastName columns the values of the FullName or LastNameFirstName fields are not updated. The solution for this is to use a trigger. The above sql update statement could be used in a trigger.

An a script to create an equivalent update statement for every insert and update of the StudentHistory is shown below. The name of the trigger is ‘UpdateFullName’ which fires every time there’s an Insert or Update on the StudentHistory table.

CREATE TRIGGER UpdateFullName ON StudentHistory
FOR INSERT, UPDATE
AS
Update StudentHistory
set FullName = {fn CONCAT({fn RTRIM(FirstName)},{fn CONCAT(' ',{fn RTRIM(LastName)})})},
LastNameFirstName = {fn CONCAT({fn CONCAT({fn RTRIM(LastName)},', ')},{fn RTRIM(FirstName)})}

MSSQL 7.0 triggers are very useful in automating updates of dependent fields. Another solution to update dependent fields is to use a calculated column which is usually created together with table creation. I have just shown a simple example of the use of trigger, but there are even complex and really powerful things that could be done with the help of triggers.