![]() ![]() #inserted and #deleted) in the main trigger code, and those should be the tables referenced by the dynamic SQL. PRINT sp_executesql BIT OUTPUT', = OUTPUTĮDIT: As noted by others, the INSERTED and DELETED tables will not be accessible within the dynamic SQL context, so their contents would first need to be copied to temporary tables (e.g. ![]() ', N' ') + N'SELECT ID FROM ' + TableName INSERT INTO JoinTables VALUES ('Table1'),('Table2'),('Table3') IF OBJECT_ID('JoinTables') IS NOT NULL DROP TABLE JoinTables This should generate a dynamic SQL command for you with all the tables in "JoinTables" (I used McNets's answer as basis): /* This Script can also be use to Dynamically get TableName and Join them. You can tell number of rows in each table and whether Id in each table is Clustered Index or not. If inserted table contain less rows like 5,10 or even 20 then it will perform ok. If you want to write same logic in UDF or Procedure then in place of inserted table you can use main table name like tbl1, tblM. Then similarly create trigger for table tblM. Where top (select SqlCol+ ' ' from cte for xml (select ','+ValueCol from cte for xml path('')) Inerted/Deleted Table cannot be use in dynamic SqlĬONCAT(' left join ',secondtable ,' t',obID,' on ',' t',obID,'.id',' = ','i.id') SqlCol How many record can each firsttable like tbl1 have ? 4-5 tables ? Dynamic query can be created in this manner, Your query would look like this: SELECT g.groupid, COUNT (m.memberid) AS members FROM groups AS g LEFT JOIN groupmembers AS m USING (groupid) GROUP BY g. Sample data of joinTable, create table #oinTables(obID int,firsttable varchar(20),secondtable varchar(20)) 114 MySQL use HAVING statement for this tasks. Trigger is best suited for your Requirement than Function. First of all, your requirement is best suited for Instead of Trigger. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |