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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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
Vitor MontalvãoMSSQL 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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ãoMSSQL 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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.