Trigger to rollback if no PK assigned

  • 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!!

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

  • 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
  • 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!

  • 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:

  • 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


    --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!

  • 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!

  • 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


    --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!

  • 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

  • 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!

  • 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


    --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!

  • 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.

  • 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>

  • @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 .

    @john-2;

    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


    --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!

  • 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