SQL Triggers:
A trigger is a special kind of stored procedure that is executed automatically when a user attempts the specified data-modification statement on the specified table. Triggers are often used for enforcing business rules and data integrity. Referential integrity can be defined by using FOREIGN KEY constraints with the CREATE TABLE statement. If constraints exist on the "trigger table," they are checked prior to the trigger execution. If constraints are violated, the trigger is not run.The syntax for creating triggers is
CREATE TRIGGER [owner.]trigger_name
ON [owner.]table_name
FOR {INSERT, UPDATE}[WITH ENCRYPTION]
AS
IF UPDATE (column_name)[{AND | OR} UPDATE (column_name)...] sql_statements
where
trigger_name:Is the name of the trigger. A trigger name must conform to the rules for identifiers and must be unique within the database.
table_name:Specifies the table on which the trigger will be executed
INSERT, UPDATE, DELETE:Are keywords that specify which data modification statements, when attempted against this table, will activate the trigger. Any combination (in any order) of these are allowed in the trigger definition.
ENCRYPTION:Encrypts the syscomments entries that contain the text of the CREATE TRIGGER statement.
AS sql_statements:Specify trigger conditions and actions. Trigger conditions specify additional criteria that determine whether the attempted INSERT, DELETE, or UPDATE statements will cause the trigger action(s) to be carried out.
The trigger actions specified in the SQL statements go into effect when the user action (UPDATE, INSERT, or DELETE) is attempted. If multiple trigger actions are specified, they are grouped within a BEGIN...END block.
Triggers can include any number and kind of SQL statements but should not include the SELECT statement. A trigger is meant to check or change data based on a data modification statement; it should not return data to the user. The SQL statements in a trigger often include control-of-flow language.
This trigger is used to insert and update values into a datetime field when the user inserts data into date field(VARCHAR) and the time field(VARCHAR). This trigger also uses the CONVERT function to convert the varchar datatype to the datetime datatype.
CREATE TRIGGER trg_datetime_insertON dbo.stroke_data
FOR INSERT,UPDATE
AS
UPDATE stroke_data SET onset_datetime=CONVERT(DATETIME,onset_date+''+onset_time)
UPDATE stroke_data SET er_datetime=CONVERT(DATETIME,er_date+''+er_time)
UPDATE stroke_data SET exam_datetime=CONVERT(DATETIME,exam_date+''+exam_time)
UPDATE stroke_data SET first_datetime=CONVERT(DATETIME,first_date+''+first_time)
UPDATE stroke_data
SET consult_called_dt=CONVERT(DATETIME,consult_called_date+''+consult_called_time)
UPDATE stroke_data
SET consult_arrived_dt=CONVERT(DATETIME,consult_arrived_date+''+consult_arrived_time)
GO
This example is an addition to the previous one. In this trigger, we not only update the datetime field for more than one column but also check to see if the date is null. When it is null, we write a null to the respective datetime field. Here we use the while..loop to go through each record
CREATE TRIGGER trg_time4 ON dbo.tbl_time2 FOR INSERT,UPDATE ASdeclare
@date varchar(20),@time varchar(20),@MAX_ID INT,@I INT
SELECT @I=1
SELECT @MAX_ID=MAX(int_id) FROM tbl_time2
UPDATE tbl_time2
SET onset_datetime=CONVERT(DATETIME,onset_date+''+onset_time)
UPDATE tbl_time2
SET er_datetime=CONVERT(DATETIME,er_date+''+er_time)
UPDATE tbl_time2
SET exam_datetime=CONVERT(DATETIME,exam_date+''+exam_time)
UPDATE tbl_time2
SET first_datetime=CONVERT(DATETIME,first_date+''+first_time)
WHILE @I<=@MAX_ID
BEGIN
SELECT @date=onset_date FROM tbl_time2 WHERE int_id=@I
SELECT @time=onset_time FROM tbl_time2WHERE int_id=@I
IF (@date is null)
BEGIN
UPDATE tbl_time2 SET onset_datetime=NULL WHERE int_id=@I
END
SELECT @date=er_date FROM tbl_time2 WHERE int_id=@I
SELECT @time=er_time FROM tbl_time2 WHERE int_id=@I
IF (@date is null) OR (@time is null)
BEGIN
UPDATE tbl_time2 SET er_datetime=NULL WHERE int_id=@I
END
SELECT @date=exam_date FROM tbl_time2 WHERE int_id=@I
SEL ECT @time=exam_time FROM tbl_time2 WHERE int_id=@I
IF (@date is null)
BEGIN
UPDATE tbl_time2 SET exam_datetime=NULL WHERE int_id=@I
END
SELECT @date=first_date FROM tbl_time2 WHERE int_id=@I
SELECT @time=first_time FROM tbl_time2 WHERE int_id=@I
IF (@date is null)
BEGIN
UPDATE tbl_time2 SET first_datetime=NULL WHERE int_id=@I
END
SELECT @I=@I+1
END
GO
Remarks:
One of the problems we encountered with these triggers was that it would write Jan 1, 1900 12:00 AM in the datetime field whenver there was a NULL in the date and time fields. The problem was solved using the loop method. However my boss Dick did not want the trigger to loop through every single record. So we decided to write the trigger without the loop
We tried many different approaches to tackle the problem without much success. Initially we used if..then statements along with the convert function to write NULLS instead of Jan 1 1900 12:00 AM. This was successful in cases where we enter nulls initially. If the user enters the correct format for date and time, the datetime columns would revert to Jan 1 1900 12:00 AM. One of the reasons why this occurs has to do with the UPDATE..SETcommand. When you use this command without the WHERE clause, SQL Server updates all the records in the column. Since we did not want to use the loop, we could not find a way to use the WHERE clause effectively. This was very frustrating and we don't have a solution for this problems yet.