troubleshooting Question

trigger to create a view when table is created

Avatar of Fay A
Fay A asked on
Microsoft SQL Server
10 Comments2 Solutions160 ViewsLast Modified:
Hi All, I am working on a trigger that works good on the same database. If I run these triggers it does create a view, alters and deletes on the same database, But I would like this trigger to run on SQL09 box when a new table is created  (pipelinetrueup database) and then create a view on SQL04 box under (ODS database). Is this possible?

here is my create trigger stmt:

USE [PipelineTrueUp]
GO

/****** Object:  DdlTrigger [trg_pipeline_orchestration_create]    Script Date: 4/9/2018 1:30:37 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO





CREATE TRIGGER [trg_pipeline_orchestration_create]
            ON DATABASE
            FOR CREATE_TABLE
            AS
       -- SET XACT_ABORT OFF;
            SET NOCOUNT ON
            DECLARE @data XML
            SET @data = EVENTDATA()
            DECLARE @schemaName VARCHAR(250) =  @data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'varchar(250)')
            DECLARE @tableName VARCHAR(250) =  @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(250)')
            DECLARE @SQL AS NVARCHAR(4000)
            IF(@schemaName != 'tmp' AND @tableName NOT LIKE 'voicerecognition%')
            BEGIN
                  SET @SQL = ' CREATE VIEW pipeline.vw_' + @tableName + ' as SELECT a.* From PipelineTrueup.' + @schemaName + '.' + @tableName + ' a WITH (NOLOCK)'
                  --EXEC ODS.dbo.sp_executesql @SQL
                  EXEC (@sql) AT ATLASBI
            END
        IF(@schemaName != 'tmp' AND @tableName LIKE 'voicerecognition%')
            BEGIN
                  SET @SQL = ' CREATE VIEW voicerecognition.vw_' + @tableName + ' as SELECT a.* From PipelineTrueup.' + @schemaName + '.' + @tableName + ' a WITH (NOLOCK);'
                  --EXEC ODS.dbo.sp_executesql @SQL
                  EXEC (@sql) AT ATLASBI
            END
GO

DISABLE TRIGGER [trg_pipeline_orchestration_create] ON DATABASE
GO
SOLUTION
Join our community to see this answer!
Unlock 2 Answers and 10 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 10 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros