EXPERT RESPONSE
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 () )
|
