5.3 Enforcing Granularity Rules5.3.1 ProblemAs a designer of a database, you don't trust your user -interface programmers, and you want to explicitly enforce the granularity of one day in your database. Bottom line: you are using the DATETIME to store dates, and you want to prevent programmers from mistakenly storing time-of-day values in your DATETIME fields. 5.3.2 SolutionUse a trigger to intercept all inserts and updates and have that trigger remove any inadvertent time-of-day values: CREATE TRIGGER ContractorSchedulesUpdate ON ContractorsSchedules FOR UPDATE, INSERT AS UPDATE ContractorsSchedules SET JobStart=CONVERT(CHAR(10),i.JobStart,120), JobEnd=CONVERT(CHAR(10),i.JobEnd,120) FROM ContractorsSchedules c, inserted i WHERE c.JobId=i.JobId With this trigger in place, programmers are restricted to storing only date values in the JobStart and JobEnd columns . They can try to insert a date and a time, but the time will be ignored. For example, consider the following INSERT statement: INSERT INTO ContractorsSchedules(JobID, ContractorID, JobStart, JobEnd, JobType) VALUES('', 'Cindy', '2001-1-1 05:12','2001-1-10 19:15', 'H') Even though this INSERT statement specifies both a date and a time for JobStart and JobEnd, only the dates were accepted: SELECT ContractorId, JobStart, JobEnd FROM ContractorsSchedules WHERE ContractorId='Cindy' ContractorId JobStart JobEnd ------------ ----------------------- ----------------------- Cindy 2001-01-01 00:00:00.000 2001-01-10 00:00:00.000 As you can see, the server cut off the time information that was mistakenly included in the INSERT statement.
5.3.3 DiscussionThe trigger brings additional overhead to INSERT and UPDATE operations; however, it provides you with the security of knowing for sure that all temporal information is stored with the required granularity. You control the granularity by adjusting the constant in the CONVERT clause. For example, if you want to set the granularity to hours, you would extend the constant by three more characters : CREATE TRIGGER ContractorSchedulesUpdate ON ContractorsSchedules FOR UPDATE, INSERT AS UPDATE ContractorsSchedules SET JobStart=CONVERT(CHAR(13),i.JobStart,121)+':00', JobEnd=CONVERT(CHAR(13),i.JobEnd,121)+':00' FROM ContractorsSchedules c, inserted i WHERE c.JobId=i.JobId With this trigger in place and using the same insert as shown in the recipe, the results will be as follows : ContractorId JobStart JobEnd ------------ ----------------------- ----------------------- Cindy 2001-01-01 05:00:00.000 2001-01-10 19:00:00.000 Setting the scope of the CHAR type effectively chops off the unwanted characters from the ISO format YYYY-MM-DD HH:MI:SS.MMM, so that we are left with YYYY-MM-DD HH. However, after that we are violating the required ISO format, so we add the string ':00' to comply with the required form, which requires at least the minutes along with the hour . In this way, you can easily restrict the granularity in a table to any degree you desire .
|