Create trigger to add extra columns for time dimensional table

create_dim_time_table.sql

USE [datamart]
GO

/****** Object:  Table [dbo].[dim_time]    Script Date: 5/14/2024 5:44:04 PM ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[dim_time]') AND type in (N'U'))
DROP TABLE [dbo].[dim_time]
GO

/****** Object:  Table [dbo].[dim_time]    Script Date: 5/14/2024 5:44:04 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[dim_time](
	[dim_time_id] [int] NOT NULL,
	[year] [int] NOT NULL,
	[year_month_str] [nchar](8) NOT NULL,
	[year_month_number] [int] NOT NULL,
	[day_of_year] [int] NOT NULL,
	[day_of_year_number] [int] NOT NULL,
	[month_number] [int] NOT NULL,
	[day_of_month] [int] NOT NULL,
	[day_of_week_str] [nchar](3) NOT NULL,
	[day_of_week_number] [int] NOT NULL,
	[the_date] [datetime2](7) NOT NULL,
 CONSTRAINT [PK_dim_time] PRIMARY KEY CLUSTERED 
(
	[dim_time_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

trigger_instead_of_insert_extra_date_fields.sql

USE [datamart]
GO
-- =============================================
-- Author:		Patrik Lindstrom
-- Create date: 2024-05-14
-- Description:	Update extra date columns in time dimensional table to make charthing easier for eg PowerBi
-- inspired by https://www.sqlbi.com/articles/creating-a-simpler-and-chart-friendly-date-table-in-power-bi/
-- you only have to insert the dim_time_id and the_date columns. 
-- =============================================
CREATE TRIGGER [dbo].[instead_of_insert_extra_date_fields] 
   ON  [dbo].[dim_time] 
   INSTEAD OF INSERT
AS 
BEGIN
	IF @@ROWCOUNT = 0 return
	INSERT INTO [dbo].[dim_time]
           ([dim_time_id]
           ,[year]
           ,[year_month_str]
           ,[year_month_number]
           ,[day_of_year]
           ,[day_of_year_number]
           ,[month_number]
           ,[day_of_month]
           ,[day_of_week_str]
           ,[day_of_week_number]
           ,[the_date])
SELECT 
		i.[dim_time_id]
      , Year (i.the_date) as [Year] 
      , LEFT(DateName(year, (i.the_date)) +'-'+ DateName( month , i.the_date),8) as  [year_month_str]
	  , (Year ( i.the_date ) * 12) + Month ( i.the_date )as [year_month_number]
	  , DateName(dayofyear,    i.the_date )as  [day_of_year]
      , YEAR ( i.the_date ) * 365 + DateName(dayofyear,    i.the_date ) as  [day_of_year_number]
      , Month (i.the_date) as [month_number]
      , Day (  i.the_date )  as [day_of_month]
	  , Format(GetDate(),'ddd')  as [day_of_week_str]
	  , [day_of_week_number]=  DATEPART(dw,GETDATE())
      ,i.the_date
	   FROM Inserted i
END
GO

ALTER TABLE [dbo].[dim_time] ENABLE TRIGGER [instead_of_insert_extra_date_fields]
GO
USE [datamart]
GO

/* Test the trigger
 insert into dim_time (dim_time_id, the_date) values(4, '2024-05-14')
 select * from dim_time
 Result:
dim_time_id year        year_month_str year_month_number day_of_year day_of_year_number month_number day_of_month day_of_week_str day_of_week_number the_date
----------- ----------- -------------- ----------------- ----------- ------------------ ------------ ------------ --------------- ------------------ ---------------------------
4           2024        2024-May       24293             135         738895             5            14           Tue             3                  2024-05-14 00:00:00.0000000
*/