create a trigger

I am trying to make a trigger (create/alter and delete) when a table is created on database1 on sqlbox1 then I want a view of this table to be created on database2 on sqlbox2 (a different database box)

any ideas?

thanks.
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.

pcelbaCommented:
Create a Linked server on sqlbox2 which may see all the tables on sqlbox1. No need to create a view.
Fay ADBAAuthor Commented:
Well for our needs, we need to create a view.
Mark WillsTopic AdvisorCommented:
You want DDL triggers
CREATE TRIGGER my_meaningful_trigger_name   
ON DATABASE   
FOR CREATE_TABLE               -- , ALTER_TABLE   
AS   
  create view (etc)
;  

Open in new window

Have a read of https://docs.microsoft.com/en-us/sql/relational-databases/triggers/ddl-triggers
Do you know how to create a view ? https://docs.microsoft.com/en-us/sql/t-sql/statements/create-view-transact-sql
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.

pcelbaCommented:
Better approach would be to create a table replica on the second server but if you are tied to the view then you may try following:

Create 2 Linked servers:
1. sqlbox2 linked from sqlbox1  ... named LinkedSQLbox2
2. sqlbox1 linked from sqlbox2  ... named LinkedSQLbox1

Create a DDL trigger on sqlbox1 which could define the view:
DECLARE @SQLcmd VARCHAR(1000);
SET @SQLcmd = 'CREATE VIEW xxx AS SELECT * FROM [LinkedSQLbox1].[Database].[dbo].[NewTable]';
EXEC (@SQLcmd) AT [LinkedSQLbox2];

I am not sure whether linked servers are allowed inside DDL triggers and whether they can be used for the view creation but I am sure you'll need Linked servers to access/execute code on a different SQL Server.

Alternate solution:
Create a job on the sqlbox2 which may check the tables on the sqlbox1 periodically (through the Linked server) and create appropriate view when a new table appears.

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:
yes, I would like to get more attention to the question that I got in return by posting it differently.
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
Databases

From novice to tech pro — start learning today.