ict.ken.be

Delivering solid user friendly software solutions since the dawn of time.

Automatically add users and fields to new list

Categories: Messagent

/****** 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