#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, Sample data of joinTable, create table #oinTables(obID int,firsttable varchar(20),secondtable varchar(20)) Hence my need to check if table (B) exists. My problem arises when there are no records retrieved due to the where conditions thus the table is not created.
Trigger is best suited for your Requirement than Function. After transposing my data I actually save the results into a table (B) so I can join with my main table (A). First of all, your requirement is best suited for Instead of Trigger.
0 kommentar(er)
