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 () )
Dig Deeper on SQL-Transact SQL (T-SQL)
Related Q&A from Roman Rehak, Contributor
There are a few things you can do to tune SQL Server for improved reporting performance. Here are some generic and Reporting Services-specific ...continue reading
Learn about working with image files in SQL Server, including advice on using queries to locate them, in this expert answer.continue reading
Is a license needed for a standby server if it's used for SQL Server replication? Get that answer and a comparison of SQL Server log shipping to ...continue reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.