trigger to create a view when table is created

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
Fay ADBAAsked:
Who is Participating?
 
Vitor MontalvãoConnect With a Mentor MSSQL Senior EngineerCommented:
It may have with the Linked Server name (bold below):
EXEC (@sql) AT ATLASBI

Check where that Linked Server is pointing to.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
I'm wondering why do you need a View that's simple return all data from a table?
0
 
Fay ADBAAuthor Commented:
that's where we point our users to run reports, view on SQL04 box under ODS database. There are many tables created daily on SQL09 box which is the master in availability groups so we don't want users running or altering tables there for their needs.
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Looks like you're not using the permissions correctly. Why are you giving users the permissions to perform changes?
By the way, if you're use SQL Server Availability Groups, you can point the reports to run on a secondary Replica. Secondary Replicas are read only so even they want, they can't perform changes on the database.
0
 
Fay ADBAAuthor Commented:
Well, I know. There are other job duties where we need these views to be created. If there is table created on SQL09 box under pipelinetrueup database then we need a view to be created on a SQL04 box under ODS database. Is that doable under a trigger?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
What's wrong with your code?
0
 
Fay ADBAAuthor Commented:
it works on the same box , somehow I need to change the code so the view gets created on SQL04 box instead.
0
 
Fay ADBAAuthor Commented:
It is pointing to some test server. Do you think if I change this to a server where I need the view to be created would that work?
0
 
Fay ADBAAuthor Commented:
Also, these two servers where i want this are part of AG.
0
 
Vitor MontalvãoConnect With a Mentor MSSQL Senior EngineerCommented:
Do you think if I change this to a server where I need the view to be created would that work?
Or you change or you create a new Linked Server to point to the server that you really want.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.