/****** We will add a database trigger that will alter the table on creation ******/
USE [DB_MESSAGENT]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [ddltrg_CREATE_TABLE] ON DATABASE
FOR CREATE_TABLE
AS
SET NOCOUNT ON
DECLARE @data xml
SET @data = EVENTDATA()
DECLARE @objname sysname
SET @objname = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)')
--PRINT @objname
IF LEFT(@objname,6) = 'USERS_'
BEGIN
DECLARE @sql VARCHAR(MAX)
SET @sql = 'ALTER TABLE [DBA_MESSAGENT].[' + @objname + '] ALTER COLUMN [MAIL] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL'
EXECUTE (@sql)
SET @sql = 'ALTER TABLE [DBA_MESSAGENT].[' + @objname + '] ADD [FIRSTNAME] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL'
EXECUTE (@sql)
SET @sql = 'ALTER TABLE [DBA_MESSAGENT].[' + @objname + '] ADD [LANG] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL'
EXECUTE (@sql)
SET @sql = 'ALTER TABLE [DBA_MESSAGENT].[' + @objname + '] ADD [DATECREATION] [datetime] NOT NULL CONSTRAINT [DF_' + @objname + '_DATECREATION] DEFAULT (getdate())'
EXECUTE (@sql)
SET @sql = 'ALTER TABLE [DBA_MESSAGENT].[' + @objname + '] ADD [DATEMODIFIED] [datetime] NOT NULL CONSTRAINT [DF_' + @objname + '_DATEMODIFIED] DEFAULT (getdate())'
EXECUTE (@sql)
SET @sql = 'INSERT INTO [DBA_Messagent].[' + @objname + '] ([MAIL],[NAME],[TESTUSER],[ONLY_TEXT],[FIRSTNAME],[LANG]) VALUES (''test1@tester.be'', ''Van Gilbergen (Dutch Text Only)'', 1, 0, ''Ken'', ''NL'')'
EXECUTE (@sql)
SET @sql = 'INSERT INTO [DBA_Messagent].[' + @objname + '] ([MAIL],[NAME],[TESTUSER],[ONLY_TEXT],[FIRSTNAME],[LANG]) VALUES (''test1@tester.be'', ''Van Gilbergen (Dutch)'', 1, 1, ''Ken'', ''NL'')'
EXECUTE (@sql)
SET @sql = 'INSERT INTO [DBA_Messagent].[' + @objname + '] ([MAIL],[NAME],[TESTUSER],[ONLY_TEXT],[FIRSTNAME],[LANG]) VALUES (''test1@tester.be'', ''Van Gilbergen (French)'', 1, 1, ''Ken'', ''FR'')'
EXECUTE (@sql)
SET @sql = 'INSERT INTO [DBA_Messagent].[' + @objname + '] ([MAIL],[NAME],[TESTUSER],[ONLY_TEXT],[FIRSTNAME],[LANG]) VALUES (''test1@tester.be'', ''Van Gilbergen (English)'', 1, 1, ''Ken'', ''EN'')'
EXECUTE (@sql)
SET @sql = 'INSERT INTO [DBA_Messagent].[' + @objname + '] ([MAIL],[NAME],[TESTUSER],[ONLY_TEXT],[FIRSTNAME],[LANG]) VALUES (''test2@tester.be'', ''Rotthier (Dutch Text Only)'', 1, 0, ''Maarten'', ''NL'')'
EXECUTE (@sql)
SET @sql = 'INSERT INTO [DBA_Messagent].[' + @objname + '] ([MAIL],[NAME],[TESTUSER],[ONLY_TEXT],[FIRSTNAME],[LANG]) VALUES (''test2@tester.be'', ''Rotthier (Dutch)'', 1, 1, ''Maarten'', ''NL'')'
EXECUTE (@sql)
SET @sql = 'INSERT INTO [DBA_Messagent].[' + @objname + '] ([MAIL],[NAME],[TESTUSER],[ONLY_TEXT],[FIRSTNAME],[LANG]) VALUES (''test2@tester.be'', ''Rotthier (French)'', 1, 1, ''Maarten'', ''FR'')'
EXECUTE (@sql)
SET @sql = 'INSERT INTO [DBA_Messagent].[' + @objname + '] ([MAIL],[NAME],[TESTUSER],[ONLY_TEXT],[FIRSTNAME],[LANG]) VALUES (''test2@tester.be'', ''Rotthier (English)'', 1, 1, ''Maarten'', ''EN'')'
EXECUTE (@sql)
SET @sql = 'CREATE TRIGGER [DBA_MESSAGENT].[trig_update_' + @objname + '] ON [DBA_MESSAGENT].[' + @objname + '] AFTER UPDATE AS
BEGIN
SET NOCOUNT ON;
IF NOT UPDATE(DATEMODIFIED)
BEGIN
UPDATE [DBA_MESSAGENT].[' + @objname + ']
SET DATEMODIFIED=getdate()
FROM [DBA_MESSAGENT].[' + @objname + '] INNER JOIN deleted d ON [DBA_MESSAGENT].[' + @objname + '].id = d.id
END
END'
EXECUTE (@sql)
--PRINT @sql
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
DISABLE TRIGGER [ddltrg_CREATE_TABLE] ON DATABASE
GO
ENABLE TRIGGER [ddltrg_CREATE_TABLE] ON DATABASE
GO