July 28, 2009 at 11:22 am
in my environment we have a policy that each and every table should have these 2 columns
1) creation_date datetime Not Null
2) last_modified_date datetime NOT NULL
I also need to make sure they have only these datatype(datetime) and both must be not null
Also column names should be lower case only
--Developers are not follo=wing these policies
I need to create a trigger that will check weather these 2 columns are there with not null and same datatype then only
create this table.
--I have created a trigger that captures whole create table event in temp table. like this
CREATE TABLE [dbo].[test]( test_id int IDENTITY(1,1) NOT NULL, [test_name] [varchar](150) NOT NULL, [creation_date] [datetime] NOT NULL, [last_modified_date] [datetime] NOT NULL) ON [PRIMARY]
How can I search above statement so that i can make sure these column names are there,they are lower case and have these datatypes with not null
i appreciate ur ideas.
Thanks
July 28, 2009 at 12:20 pm
Untested, but something like this I think will work:SELECT
*
FROM
INFORMATION_SCHEMA.TABLES AS T
LEFT OUTER JOIN
(
SELECT
Table_Name
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
DATA_TYPE = 'datetime'
AND IS_NULLABLE = 'NO'
AND
(
Column_Name = 'creation_date'
OR Column_Name = 'last_modified_date'
)
GROUP BY
Table_Name
HAVING
COUNT(*) = 2
) AS C
ON T.Table_Name = C.Table_Name
WHERE
C.Table_Name IS NULL
AND T.Table_Type = 'BASE TABLE'
July 28, 2009 at 12:24 pm
Well thanks but i need to create a DDL trigger means i need to prevent user from creating table if he is not
creating it according to my standard.
--this code will help me but after the creation of the table
July 28, 2009 at 12:31 pm
probably a misstatment, but you can't use a trigger i think, but you can certainly use a script to test for your invalid columns:
here's an example of the first two requirements...you can then use the same logic to test is_nullable = 0 and also whether
sys.columns.name collate SQL_Latin1_General_Cp1_CS_AS 'creation_date' collate SQL_Latin1_General_Cp1_CS_AS
--must have both columns
SELECT '[' + schema_name(OBJS.schema_id) + ']' + '[' + OBJS.name + ']'
+ 'does not have both required columns '
from sys.objects OBJS
LEFT OUTER JOIN (select object_id,name from sys.columns where name = 'creation_date') FIRSTCOL
ON OBJS.object_id=FIRSTCOL.object_id
LEFT OUTER JOIN (select object_id,name from sys.columns where name = 'last_modified_date') SECONDCOL
ON OBJS.object_id=SECONDCOL.object_id
where OBJS.type='U'
AND (FIRSTCOL.object_id IS NULL OR SECONDCOL.object_id IS NULL)
--check the datatype
SELECT '[' + schema_name(OBJS.schema_id) + ']' + '[' + OBJS.name + ']'
+ 'does not have both required datatype '
from sys.objects OBJS
LEFT OUTER JOIN (select object_id,name,system_type_id from sys.columns where name = 'creation_date') FIRSTCOL
ON OBJS.object_id=FIRSTCOL.object_id
LEFT OUTER JOIN (select object_id,name,system_type_id from sys.columns where name = 'last_modified_date') SECONDCOL
ON OBJS.object_id=SECONDCOL.object_id
where OBJS.type='U'
AND FIRSTCOL.object_id IS NOT NULL
AND SECONDCOL.object_id IS NOT NULL
and (TYPE_NAME(FIRSTCOL.system_type_id) 'datetime' OR TYPE_NAME(SECONDCOL.system_type_id) 'datetime')
--check the isnullable
--check the case by using collation
Lowell
July 28, 2009 at 12:35 pm
I was thinking something similar, but I would add a Having clause like HAVING Min(IsNull(Column_Name,'') 'creation_date' OR Max(IsNull(Column_Name, '') 'last_modified_date' You may also want to specify the collation for these literals to ensure that they are lowercase.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
July 28, 2009 at 12:41 pm
Not 100% sure about being able to do the check in a trigger. As far as I know you can get at the CREATE statement.. But, then I think you'd have to parse the text in order to determine if it meets your requirements.. For example:CREATE TRIGGER Test
ON DATABASE
FOR CREATE_TABLE
AS
SELECT EVENTDATA()
; One of the elements is CommandText that you can parse. But, maybe there is a better/good way to go about that.
how about issuing an edict that people will lose their jobs if they cannot comply with the standards? That always seems to get peoples attention. 🙂
July 28, 2009 at 12:50 pm
I have created this trigger but its still not what i want. i guess its hard to achieve this.
ALTER TRIGGER [check_column_names]
ON DATABASE
for CREATE_TABLE
AS
BEGIN
DECLARE @data XML;
--DECLARE @name varchar(20)
--SET @name = 'db_proc_exec'
SET @data = EVENTDATA();
Create table #temp(sql_cmd nvarchar(4000))
Insert #temp(sql_cmd) values(@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(4000)') )
select * from #temp
Begin
select sql_cmd from #temp where
sql_cmd like '%creation_date%' AND
sql_cmd like '%last_modified_date%'
IF @@rowcount =0
--IF (SELECT @name) (SELECT * from #temp Where sql_cmd not like 'db_proc_exec%')
BEGIN
RAISERROR ('Please add 5 mandatory columns' ,16, 1 )
ROLLBACK
END
END
END
GO
July 28, 2009 at 12:52 pm
I was messing around a bit since I haven't mess with DDL triggers before.. this seems to work:ALTER TRIGGER Test
ON DATABASE
FOR CREATE_TABLE
AS
IF NOT EXISTS
(
SELECT
Table_Name
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
Table_Name = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(128)')
AND DATA_TYPE = 'datetime'
AND IS_NULLABLE = 'NO'
AND
(
Column_Name COLLATE SQL_Latin1_General_CP1_CS_AS = 'creation_date' COLLATE SQL_Latin1_General_CP1_CS_AS
OR Column_Name COLLATE SQL_Latin1_General_CP1_CS_AS = 'last_modified_date' COLLATE SQL_Latin1_General_CP1_CS_AS
)
GROUP BY
Table_Name
HAVING
COUNT(*) = 2
)
BEGIN
RAISERROR ('Table structure invalid blah blah....', 16, 1);
ROLLBACK;
END
; EDIT: You might need to add in the Schema incase you are using more than just DBO...
July 28, 2009 at 1:06 pm
this post from microsoft says
http://msdn.microsoft.com/en-us/library/ms189799.aspx
Because SQL Server does not support user-defined triggers on system tables,
we recommend that you do not create user-defined triggers on system tables.
July 28, 2009 at 1:11 pm
There are no system tables being triggered, so you should be fine.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply