question

HemantSudehely-0557 avatar image
0 Votes"
HemantSudehely-0557 asked AnuragSharma-MSFT answered

Sql Managed Instance - Implement trigger between multiple database

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?

azure-sql-databaseazure-migrate
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

HemantSudehely-0557 avatar image
0 Votes"
HemantSudehely-0557 answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AnuragSharma-MSFT avatar image
0 Votes"
AnuragSharma-MSFT answered

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.




    5 |1600 characters needed characters left characters exceeded

    Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.