asullivan62
asked on
SQL trigger, checking if table exists
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]','nvar char(max)' )
,@SchemaName = EVENTDATA().value('(/EVENT _INSTANCE/ SchemaName )[1]','nva rchar(max) ')
,@ObjectName = EVENTDATA().value('(/EVENT _INSTANCE/ ObjectName )[1]','nva rchar(max) ')
,@ObjectType = EVENTDATA().value('(/EVENT _INSTANCE/ ObjectType )[1]','nva rchar(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(varch ar(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]','nvar char(max)' )
,@SchemaName = EVENTDATA().value('(/EVENT _INSTANCE/ SchemaName )[1]','nva rchar(max) ')
,@ObjectName = EVENTDATA().value('(/EVENT _INSTANCE/ ObjectName )[1]','nva rchar(max) ')
,@ObjectType = EVENTDATA().value('(/EVENT _INSTANCE/ ObjectType )[1]','nva rchar(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(varch ar(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
/****** 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
,@SchemaName = EVENTDATA().value('(/EVENT
,@ObjectName = EVENTDATA().value('(/EVENT
,@ObjectType = EVENTDATA().value('(/EVENT
-- 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(varch
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
,@SchemaName = EVENTDATA().value('(/EVENT
,@ObjectName = EVENTDATA().value('(/EVENT
,@ObjectType = EVENTDATA().value('(/EVENT
-- 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(varch
--SET ANSI_PADDING OFF
end
go
--------------------------
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ENABLE TRIGGER [trgMonitorChange] ON DATABASE
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Well I am not sure why that made the difference but it did and it works great now thanks.
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..##Monit orChange'' , 'U')
http://msdn.microsoft.com/en-us/library/ms190324.aspx
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..##Monit
http://msdn.microsoft.com/en-us/library/ms190324.aspx
ASKER