INSTEAD OF trigger to update a SQL Server table

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 performance reason) 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.'

    Requires Free Membership to View

    Login

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

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top