Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Trigger to rollback if no PK assigned


Trigger to rollback if no PK assigned

Author
Message
John Waclawski
John Waclawski
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

Group: General Forum Members
Points: 57 Visits: 415
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!!

======================================================
John
SQL Server 200x Newb and proud of it! :-D
======================================================
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16580 Visits: 17024
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Luis Cazares
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8550 Visits: 18142
Maybe you could use a DDL trigger or Policy-Based Management.


Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14959 Visits: 38977
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

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

John Waclawski
John Waclawski
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

Group: General Forum Members
Points: 57 Visits: 415
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

======================================================
John
SQL Server 200x Newb and proud of it! :-D
======================================================
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14959 Visits: 38977
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 Smile

Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

John Waclawski
John Waclawski
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

Group: General Forum Members
Points: 57 Visits: 415
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!

======================================================
John
SQL Server 200x Newb and proud of it! :-D
======================================================
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14959 Visits: 38977
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,
[USER] 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 [user],
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

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Erin Ramsay
Erin Ramsay
Mr or Mrs. 500
Mr or Mrs. 500 (561 reputation)Mr or Mrs. 500 (561 reputation)Mr or Mrs. 500 (561 reputation)Mr or Mrs. 500 (561 reputation)Mr or Mrs. 500 (561 reputation)Mr or Mrs. 500 (561 reputation)Mr or Mrs. 500 (561 reputation)Mr or Mrs. 500 (561 reputation)

Group: General Forum Members
Points: 561 Visits: 1099
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
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14959 Visits: 38977
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

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search