Sql Managed Instance - Implement trigger between multiple database

Question

Hi,

I am using Azure SQL managed Instance and have two databases
Core databases
LoggingCore Database

Now I need to implement a delete audit trigger on the Core database tables and log the delete information in the "LoggingCore" database table.

Checking if this is possible or this feature is supported in the Azure SQL Managed Instance server?

Any thought?

Answer 1

Another example of requirement is
I have 2 databases. One, named Test, has a table named Vehicles. Another, named Test2 has a table named Clients.

When I insert a new record on the Vehicles table in Test, I need to update the NumVehicles field on the Client table in Test2.

Is this possible in Azure SQL Managed Instance using triggers

Answer 2

Hi @HemantSudehely-0557, yes we can create the trigger between multiple databases in same Azure Sql Managed Instance. Please check the below steps:

Create an Azure Sql Managed Instance on Azure Portal

Connect the instance through SSMS

Create a trigger in below format, need to create a linked query to update second database through first database. This is just a sample code and you can write the trigger logic as per your need. Or if there is anything specific related to code, please let us know.

CREATE TRIGGER testInsertTriggerOnAzureManagedInstance
ON [test].[dbo].[audit]
FOR INSERT AS
BEGIN

your logic
INSERT INTO dbo.audit values ('1')
INSERT INTO [test2].[dbo].[audit] values('2')
END

Execute the trigger by inserting some records in [test].[dbo].[audit] table. Both the tables should be updated.

Delete trigger can be written in same way.

Please let us know if this was the problem statement you were looking for. If not, we can discuss further and we will be glad to help you out here.

---

If an Answer is helpful, please “Accept Answer” or Up-Vote for the same which might be beneficial to other community members reading this thread.