Q

Should I use the SQL Server INSTEAD OF trigger to update a table, or a regular trigger?

Find how to use the INSTEAD OF trigger to update a SQL Server table for myDate and myTime.

I would like to create a trigger on insert, update for a table like this:

MyTable (myDate datetime, myTime datetime)

Now, to ease my SQL statements (and for performance reasons) I want to make sure the column myDate always has the time set to '00:00:00' and the myTime column always has the date set to '1/1/1900.'

You can do this in a regular trigger but an "INSTEAD OF" trigger would work even better. It helps avoid dealing with trigger recursion that you would otherwise experience since you will be modifying the underlying table. This type of trigger overrides the action of the statement that triggered it. Instead, it just executes the code inside of the trigger. Here is a sample code that stores the date and time in the format you specified, it creates a table and two INSTEAD OF triggers, one for inserts and the other one for deletes:

CREATE TABLE [dbo] . [MyTable] (
[ID] [int] IDENTITY (1,1) NOT NULL PRIMARY
KEY
CLUSTERED,
[myDate] [datetime] NOT NULL,
[myTime] [datetime] NOT NULL,
) ON [PRIMARY]
GO
CREATE TRIGGER
dbo.MyTableInsertTrigger ON MyTable
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO MyTable
(myDate, myTime)
SELECT CONVERT(VARCHAR(10), myDate, 101),
DATEADD (DD, -CAST (myTime AS FLOAT), myTime)
FROM Inserted
END
GO
CREATE TRIGGER dbo.MyTableUpdateTrigger ON MyTable
INSTEAD OF UPDATE
AS
BEGIN
SET NOCOUNT ON;
UPDATE MyTable
SET myDate = CONVERT (VARCHAR(10), Inserted.myDate, 101),
myTime = DATEADD (DD, -CAST(Inserted.myTime AS FLOAT),
Inserted.myTime)
FROM Inserted, MyTable
WHERE Inserted.ID = MyTable.ID
END
GO

You can now run these statements and verify that the date and time are stored as you wanted:

INSERT INTO MyTable
SELECT GETDATE (), GETDATE ()
SELECT * FROM MyTable
UPDATE MyTable
SET Mydate = GETDATE (),
myTime = GETDATE ()
SELECT * FROM MyTable

The code that removes the time part from myDate is fairly straightforward -- when you convert from datetime to varchar(10), the time part gets truncated. The code that sets the myTime column to '1900/1/1' is more complex. I am using the DATEADD function to subtract number of days between the base date of '1900/1/1' and the numeric value of the datetime in the myDate column. I can do this because internally dates are stored as integers. The numeric value of '1900/1/1' is 0. This code gives you the number of days between today and '1900/1/1':

SELECT DATEDIFF (DD, '1900/1/1', GETDATE () )
This was first published in December 2007

Dig deeper on SQL-Transact SQL (T-SQL)

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close