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

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]

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)

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