Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Trigger to rollback if no PK assigned Expand / Collapse
Author
Message
Posted Thursday, August 9, 2012 7:48 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 12:24 PM
Points: 55, Visits: 374
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!
======================================================
Post #1342647
Posted Thursday, August 9, 2012 7:58 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:13 PM
Points: 13,282, Visits: 12,116
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)
Post #1342659
Posted Thursday, August 9, 2012 8:00 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 11:05 AM
Points: 3,513, Visits: 7,567
Maybe you could use a DDL trigger or Policy-Based Management.


Luis C.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1342660
Posted Thursday, August 9, 2012 8:01 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:39 AM
Points: 12,906, Visits: 31,985
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1342662
Posted Thursday, August 9, 2012 8:57 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 12:24 PM
Points: 55, Visits: 374
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.



======================================================
John
SQL Server 200x Newb and proud of it!
======================================================
Post #1342724
Posted Thursday, August 9, 2012 9:00 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:39 AM
Points: 12,906, Visits: 31,985
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1342731
Posted Monday, August 13, 2012 9:46 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 12:24 PM
Points: 55, Visits: 374
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!
======================================================
Post #1344251
Posted Monday, August 13, 2012 10:01 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:39 AM
Points: 12,906, Visits: 31,985
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1344258
Posted Monday, August 13, 2012 11:17 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, August 11, 2014 12:41 PM
Points: 541, Visits: 1,045
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
Post #1344310
Posted Monday, August 13, 2012 11:36 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:39 AM
Points: 12,906, Visits: 31,985
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1344318
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse