问题描述:

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.

Ex:

  • In Table M1 I have a=1
  • In Table M2 I have b=1
  • In Table M3 I have c=2

Dynamic Query:

IF M1.a = M2.b OR M2.b <> M3.c

BEGIN

//execute some operation

END


Triggers on M1 M2 M3 M4

Create trigger tgrM1 On M1

BEGIN

DECLARE @Query NVARCHAR(MAX)

SELECT @Query = DynamicQuery FROM tblDynamic WHERE type = 'xyz'

IF @Query <> ''

BEGIN

//fire some dynamic query

END

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.

相关阅读:
Top