I have a UI where the data comes from 4 tables M1,M2,M3,M4 and I got a save button which will Update data in these four tables.
Now I have a dynamic query in another table "tblDynamic". Which have some operations to do at different places of database or even it can Send Emails.
My requirement is whenever certain values in M1,M2,M3,M4 meets certain conditions. It should trigger this dynamic query.
IF M1.a = M2.b OR M2.b <> M3.c
//execute some operation
Triggers on M1 M2 M3 M4
Create trigger tgrM1 On M1
DECLARE @Query NVARCHAR(MAX)
SELECT @Query = DynamicQuery FROM tblDynamic WHERE type = 'xyz'
IF @Query <> ''
//fire some dynamic query
My problem here is if I have trigger on both the tables
So when there is an OR condition
I have to either fire M1 tables trigger or M2 tables trigger.
What if both the conditions are true?
I get the dynamic query fired twice
how to solve this? any suggestions please let me know.
I have solved this issue by just adding a temp table to the database. And moving the trigger onto that temp table.
While the save button is clicked for M1,M2,M3,M4 to save data from the UI. The changed data is also inserted into "Mtemp" table from all four tables.
And the trigger on "Mtemp" table gets fired to perform my dynamic query operation. Using the combined data of M1, M2, M3, M4 into Mtemp.
Hope this will help someone who is facing similar issue.