Solved

SQL trigger, checking if table exists

Posted on 2010-09-02
4
378 Views
Last Modified: 2012-06-22
This trigger does not work, but the query does. I need the trigger to check if the table exists if not, it will create it.
/****** Object:  DdlTrigger [trgMonitorChange]    Script Date: 06/03/2010 08:26:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

create TRIGGER [trgMonitorChange]
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
------------------------------
if exists(select * from tempdb.sys.tables where name ='##MonitorChange')
begin
--print 'Table ##MonitorChange does exists, record inserted.'
      set nocount on
      declare @EventType varchar(100)
      declare @SchemaName varchar(100)
      declare @ObjectName varchar(100)
      declare @ObjectType varchar(100)
      SELECT
       @EventType = EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(max)')  
      ,@SchemaName = EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]','nvarchar(max)')  
      ,@ObjectName = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(max)')
      ,@ObjectType = EVENTDATA().value('(/EVENT_INSTANCE/ObjectType)[1]','nvarchar(max)')  
      -- Is the default schema used
      if @SchemaName = ' ' select @SchemaName = default_schema_name from sys.sysusers u join sys.database_principals p
                                          on u.uid = p.principal_id  where u.name = CURRENT_USER
      insert into ##MonitorChange
              select @@servername,convert(varchar(100),DB_NAME()), @EventType, @SchemaName, @ObjectName, @ObjectType, getdate(), SUSER_SNAME(), CURRENT_USER, ORIGINAL_LOGIN()  
end
else
begin
--print 'Table ##MonitorChange does not exist, Table ##MontitorChange created, record inserted.'
      SET ANSI_NULLS ON
      SET QUOTED_IDENTIFIER ON
      SET ANSI_PADDING ON
      CREATE TABLE [dbo].[##MonitorChange](
            [Server_Name] [varchar](100) NULL,
            [DB_Name] [varchar](100) NULL,
            [EventType] [varchar](100) NULL,
            [SchemaName] [varchar](100) NULL,
            [ObjectName] [varchar](100) NULL,
            [ObjectType] [varchar](100) NULL,
            [EventDate] [datetime] NULL,
            [SystemUser] [varchar](100) NULL,
            [CurrentUser] [varchar](100) NULL,
            [OriginalUser] [varchar](100) NULL
      ) ON [PRIMARY]
      
      ------------------------------
      ------------------------------
--set nocount on
--declare @EventType1 varchar(100)
--declare @SchemaName1 varchar(100)
--declare @ObjectName1 varchar(100)
--declare @ObjectType1 varchar(100)
SELECT
 @EventType = EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(max)')  
,@SchemaName = EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]','nvarchar(max)')  
,@ObjectName = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(max)')
,@ObjectType = EVENTDATA().value('(/EVENT_INSTANCE/ObjectType)[1]','nvarchar(max)')  
-- Is the default schema used
if @SchemaName = ' ' select @SchemaName = default_schema_name from sys.sysusers u join sys.database_principals p
                        on u.uid = p.principal_id  where u.name = CURRENT_USER
insert into ##MonitorChange
      select @@servername,convert(varchar(100),DB_NAME()), @EventType, @SchemaName, @ObjectName, @ObjectType, getdate(), SUSER_SNAME(), CURRENT_USER, ORIGINAL_LOGIN()  
--SET ANSI_PADDING OFF      
end
go
------------------------------
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ENABLE TRIGGER [trgMonitorChange] ON DATABASE
0
Comment
Question by:asullivan62
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 

Author Comment

by:asullivan62
ID: 33590946
The table is never created if executed in a trigger.
0
 
LVL 5

Accepted Solution

by:
almander earned 500 total points
ID: 33591719
Use

IF OBJECT_ID('tempdb..##MonitorChange'') IS NOT NULL

instead of

if exists(select * from tempdb.sys.tables where name ='##MonitorChange')
0
 

Author Closing Comment

by:asullivan62
ID: 33591998
Well I am not sure why that made the difference but it did and it works great now thanks.
0
 
LVL 5

Expert Comment

by:almander
ID: 33592316
For some crazy reason all flavors of temp tables are not stored in sys.tables, sysobjects, or information_schema.

The kicker is that ObjectId will work for any object. And also takes an optional parameter to specify the object type.

I.E.
OBJECT_ID('tempdb..##MonitorChange'', 'U')

http://msdn.microsoft.com/en-us/library/ms190324.aspx
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question