August 9, 2012 at 7:48 am
I've been tasked to create a trigger that will deny the creation of a table if no PK (Primary Key) is assigned to the newly created table. Mainly because we have over 120 lazy programmers that find it necessary to leave that little piece of information out.
Is there a way to find out if a table has a PK through the trigger & perform a rollback if there is no primary key?
In my current adventures through the internet I'm finding out that capturing the newly created tablename in a trigger is not doable. If I could capture the newly created tablename in the trigger, my problem would be solved.
Any suggestions out there? And telling all the developer to put in a PK is not an option. We've tried that & it doesn't work. Inevitably we want to put all tables in a database onto replication & this is the one way we can force the lazy developers to put PK's on their tables.
Thanks!!
August 9, 2012 at 7:58 am
I don't know how to capture your table but it seems to me that the best way to solve this is from a policy change. I don't mean to tell your developers that a PK is required. I mean to stop allowing them to create tables. I can only imagine the nightmarish structures you must have with "over 120 developers" creating their own tables. I would dial back the permissions and have sql developer or DBA create your tables for them. That way you know everything is in order. Just my 2¢.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 9, 2012 at 8:00 am
Maybe you could use a DDL trigger or Policy-Based Management.
August 9, 2012 at 8:01 am
interesting requirement;
i just tested this trigger as both ON DATABASE and ON ALL SERVER, and it worked fine in my brief testing;
i was able to create temp tables without a primary key, which i thought was important to note...it won't prevent temp tables,a nd i don't think it should, either.
the trigger is just an adaptation of my standard DDL model; there's a lot of variablesthis trigger doesn't need,but it's a nice model to build from in case of later enhancements.
CREATE TRIGGER [TR_DDL_Tables_Require_PKs]
ON DATABASE
FOR
CREATE_TABLE
AS
BEGIN
SET NOCOUNT ON
declare @ObjectDef table(definition varchar(max))
declare
@eventData XML,
@DATABASENAME SYSNAME,
@EVENTDATE DATETIME,
@USERNAME SYSNAME,
@SYSTEMUSER VARCHAR(128),
@CURRENTUSER VARCHAR(128),
@ORIGINALUSER VARCHAR(128),
@HOSTNAME VARCHAR(128),
@APPLICATIONNAME VARCHAR(128),
@SCHEMANAME SYSNAME,
@OBJECTNAME SYSNAME,
@OBJECTTYPE SYSNAME,
@EVENTTYPE VARCHAR(128),
@COMMANDTEXT VARCHAR(max),
@NAMEFORDEFINITION VARCHAR(261)
--Load Variables from the xml
SET @eventData = eventdata()
SELECT
@DATABASENAME = db_name(),
@EVENTDATE = GETDATE(),
@USERNAME = @eventData.value('data(/EVENT_INSTANCE/UserName)[1]', 'SYSNAME'),
@SYSTEMUSER = SUSER_SNAME(),
@CURRENTUSER = CURRENT_USER,
@ORIGINALUSER = ORIGINAL_LOGIN(),
@HOSTNAME = HOST_NAME(),
@APPLICATIONNAME = APP_NAME(),
@SCHEMANAME = @eventData.value('data(/EVENT_INSTANCE/SchemaName)[1]', 'SYSNAME'),
@OBJECTNAME = @eventData.value('data(/EVENT_INSTANCE/ObjectName)[1]', 'SYSNAME'),
@OBJECTTYPE = @eventData.value('data(/EVENT_INSTANCE/ObjectType)[1]', 'SYSNAME'),
@COMMANDTEXT = @eventData.value('data(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','VARCHAR(MAX)'),
@EVENTTYPE = @eventData.value('data(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(128)')
--the variables above can be used for any testing or logging of objects.
--use whichever variables are required for this trigger
IF CHARINDEX('PRIMARY KEY',@COMMANDTEXT) <=0
BEGIN
--raise an error, and rollback
RAISERROR('All CREATE TABLE commands must contain a PRIMARY KEY as part of the definition.', 16, 1);
ROLLBACK TRAN;
END --IF
END --DB TRIGGER
GO
ENABLE TRIGGER [TR_DDL_Tables_Require_PKs] ON DATABASE
GO
/* --cleanup
DISABLE TRIGGER [TR_DDL_Tables_Require_PKs] ON DATABASE
DROP TRIGGER [TR_DDL_Tables_Require_PKs] ON DATABASE
*/
Lowell
August 9, 2012 at 8:57 am
Lowell,
Thank you! This is just what we're looking for! Testing so far is working great!
Now I'll pick it apart to see how you did this.
Thanks again! Your trigger will make a lot of lazy coders work just a little harder. :w00t:
August 9, 2012 at 9:00 am
lol i was testing a different forum post and got stopped by this trigger!
that's hilarious for me.
i went ahead and dropped it on my server now 🙂
Lowell
August 13, 2012 at 9:46 am
Lowell,
This piece of code has been gold for me in a couple of other triggers. Thank you again.
The instance I am having issues with at this time is using it at this time are using it at the table level. I'm using it for "AFTER INSERT, DELETE, UPDATE" at the table level. Nothing is returning for:
@SCHEMANAME
@OBJECTNAME
@OBJECTTYPE
@COMMANDTEXT
@EVENTTYPE
Was hoping the @COMMANDTEXT would at least return what sql statement was run for those events.
Any suggestions?
Thanks again!
August 13, 2012 at 10:01 am
LOL John it's feedback like that, knowing I helped someone, that really makes my day!
inside a DML trigger, it's a differnet technique, but you bet I have examples of that too.
let me say first, you might run into permissions issues here...you can use sys.objects to get the name of the table the trigger is sitting on, as well as the schema name, but if the end user does not have permissions to the objects, they may return an error or reutrn nothing at all;
you might need to Grant View Definition ON SCHEMA::[dbo] To [YourRole]
if you have tight permissions.
for the command that is triggering the trigger, you need to use DBCC inputbuffer inside the trigger body;
EXEC sp_executesql N'DBCC INPUTBUFFER(@@spid) WITH NO_INFOMSGS'
the buffer is limited to 4000 chars, so bigger code blocks/ commands might get cut off.
here's a full example:
tCREATE TABLE WHATEVER(
WHATEVERID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
DESCRIP VARCHAR(30)
)
INSERT INTO WHATEVER(DESCRIP)
SELECT 'APPLES' UNION
SELECT 'ORANGES' UNION
SELECT 'BANANAS' UNION
SELECT 'GRAPES' UNION
SELECT 'CHERRIES' UNION
SELECT 'KIWI'
--used to capture the row id plus a bunch of audit information
CREATE TABLE [dbo].[WHATEVER_AUDIT] (
[WHATEVERID] INT NOT NULL,
[INSERTUPDATE] NVARCHAR(30) NULL,
[LASTCOMMAND] NVARCHAR(max) NULL,
[USER_NAME] NVARCHAR(256) NULL,
[SUSER_NAME] NVARCHAR(256) NULL,
[CURRENT_USER] NVARCHAR(256) NULL,
[SYSTEM_USER] NVARCHAR(256) NULL,
[SESSION_USER] NVARCHAR(256) NULL,
NVARCHAR(256) NULL,
[APPLICATION_NAME] NVARCHAR(256) NULL,
[HOST_NAME] NVARCHAR(256) NULL,
[OCCURANCE_DATE] DATETIME DEFAULT GETDATE() NOT NULL)
GO
CREATE TRIGGER TR_WHATEVER
ON WHATEVER
FOR INSERT,UPDATE
AS
BEGIN
DECLARE @INSERTUPDATE NVARCHAR(30),
@LASTCOMMAND NVARCHAR(max)
--################################################################################################
--note these two methods do not get the last command when inside a trigger;
--included for complete solution
--get the last command by the current spid:
--DECLARE @handle varbinary(64)
--SELECT @handle = MAX(sql_handle) FROM master..sysprocesses WHERE spid = @@SPID
--SELECT @LASTCOMMAND = [Text] FROM ::fn_get_sql(@Handle)
--get the last command by the current spid:
--SELECT @LASTCOMMAND = DEST.TEXT
--FROM sys.[dm_exec_connections] SDEC
--CROSS APPLY sys.[dm_exec_sql_text](SDEC.[most_recent_sql_handle]) AS DEST
--WHERE SDEC.[most_recent_session_id] = @@SPID
--################################################################################################
--because dbcc inputbuffer is limited to 4000 chars, you may need to combine this witha DML trace
--################################################################################################
DECLARE @SQLBuffer nvarchar(4000)
DECLARE @buffer TABLE (
EventType nvarchar(30),
Parameters int,
EventInfo nvarchar(4000)
)
INSERT @buffer
EXEC sp_executesql N'DBCC INPUTBUFFER(@@spid) WITH NO_INFOMSGS'
SELECT @LASTCOMMAND = EventInfo
FROM @buffer
--assume it is an insert
SET @INSERTUPDATE='INSERT'
--if there's data ind eleted, it's an update
IF EXISTS(SELECT * FROM DELETED)
SET @INSERTUPDATE='UPDATE'
--insert data that meets the criteria: the column 'description' is null
INSERT INTO [WHATEVER_AUDIT]
SELECT
INSERTED.WHATEVERID,
@INSERTUPDATE,
@LASTCOMMAND,
user_name() AS [user_name],
suser_name() AS [suser_name],
current_user AS [current_user],
system_user AS [system_user],
session_user AS [session_user],
user AS ,
APP_NAME() AS [application_name],
HOST_NAME() AS [host_name],
getdate() AS [occurance_date]
FROM INSERTED
WHERE DESCRIP IS NULL
END --TRIGGER
GO
--does not trigger audit:
INSERT INTO WHATEVER(DESCRIP)
SELECT 'CANTALOUPE' UNION
SELECT 'TANGARINES' UNION
SELECT 'PLUMS' UNION
SELECT 'PEACHES' UNION
SELECT 'BLUEBERRIES'
--triggers one row out of multi row insert
INSERT INTO WHATEVER(DESCRIP)
SELECT NULL UNION
SELECT 'TANGARINES'
--triggers one row out of multi row insert
UPDATE WHATEVER SET DESCRIP = NULL WHERE WHATEVERID IN (4,5)
SELECT * FROM WHATEVER
SELECT * FROM [WHATEVER_AUDIT]
here's the code to get the schema/table name inside the trigger:
SELECT
schema_name(schema_id),
name As TableName
from sys.objects
where object_id in(select
parent_object_id
from sys.objects
where object_id = @@PROCID)
Lowell
August 13, 2012 at 11:17 am
Hey Lowell,
Is there a way to set that DBCC inputbuffer code on the database level so that when ANY trigger fires you can get the schema name, table name, trigger name and the DBCC inputbuffer that caused that trigger to fire?
Erin
August 13, 2012 at 11:36 am
Erin Ramsay (8/13/2012)
Hey Lowell,Is there a way to set that DBCC inputbuffer code on the database level so that when ANY trigger fires you can get the schema name, table name, trigger name and the DBCC inputbuffer that caused that trigger to fire?
Erin
well I'm not sure I'm reading your question right.... this code snippet, inside any DML trigger, would get what you are asking for, but i'm not 100% sure it was you asked;
that would end up being part of your trigger model to be in every trigger, i think.
--assuming inside a trigger:
DECLARE @SQLBuffer nvarchar(4000)
DECLARE @buffer TABLE (
EventType nvarchar(30),
Parameters int,
EventInfo nvarchar(4000)
)
INSERT @buffer
EXEC sp_executesql N'DBCC INPUTBUFFER(@@spid) WITH NO_INFOMSGS'
SELECT @LASTCOMMAND = EventInfo
FROM @buffer
SELECT
SCHEMA_NAME(objz.schema_id) AS SchemaName,
objz.name AS TableName,
OBJECT_NAME(@@PROCID) AS TriggerName,
bufz.EventInfo AS LastCommand
FROM sys.objects
WHERE OBJECT_ID IN(SELECT
parent_object_id
FROM sys.objects objz
WHERE OBJECT_ID = @@PROCID)
CROSS JOIN @buffer bufz
Lowell
August 13, 2012 at 11:42 am
also you can simply create a DML trace on the database and capture all that information as part of a couple of events i think;SQL:BatchCompleted and RPC:Completed is what i typically track for any DML traces;
Lowell
August 13, 2012 at 11:51 am
Right, Lowell, that code will fire inside any DML trigger that fires but only inside triggers that contain said code. Is there an umbrella object in which it could be installed so that EVERY DML trigger that fires also fires that DBCC statement?
For example, you've got a trigger on the create table statement. Can you put a trigger on something like "database.trigger event"?
The reason I ask is that it's often REALLY annoying to try to use the trace functionality and install it in a SQL Express environment.
August 13, 2012 at 11:58 am
Lowell,
Will this code work on DELETES as well? I'm picking this apart as we speak but thought I'd ask before I spend a lot of time on this to find out it's not doable.
Thanks again! You really don't know what can of worms you have opened up for me with this code! It's almost diabolical. <place evil scientist icon here>
August 13, 2012 at 12:06 pm
@Erin my knee jerk reaction was to check if there was something in the extended events to do what you are asking, but i'm just not seeing anything;
to me it looks like DDL and other events like server events, but nothing related to DML;
it may be that my Google-Fu skills are not strong .
Yes, the events area available for DELETE and INSTEAD OF triggers as well; remember the caveat about access to sys.objects, but go ahead and get your frankenstein lab ready to play!
Lowell
August 13, 2012 at 12:26 pm
Thanks, Lowell. That was my first reaction as well, to check the extended events as it really seemed like it should be in there but I couldn't find anything about it either.
Hey Lowell, just found this :
http://www.sqlteam.com/article/advanced-sql-server-2008-extended-events-with-examples
Does it seem to you that we could do something with this in relation to monitoring triggers?
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply