0

(The below task is not for real life; this is from a job interview)

I have a DML trigger on a SQL Server 2012 table. The logic of the trigger requires to read the trigger's virtual table inserted and copy the data into a log table.

It is OK; but one of the task conditions is that this "copy the data into a log table" should be done using sp_exequtesql.

But the Dynamic SQL code in sp_exequtesql does not see the trigger’s virtual tables, does it?

Any ideas how to cope with this?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Victor Sotnikov
  • 177
  • 1
  • 2
  • 15
  • 2
    I don't think so, dynamic SQL runs as a new process, ie, its own SPID, the inserted/deleted tables belong to the transaction of the current process. They are stored in TempDB so there may be a *really hacky* way of getting access but why bother? Needing dynamic SQL in a trigger has a pretty nasty aroma too. – Stu Apr 20 '23 at 15:37
  • 1
    Why do you have to use dynamic sql in a trigger? Sounds like something has gone the wrong direction in the design here. – Sean Lange Apr 20 '23 at 15:39
  • FYI, it's `sp_executesql` *not* `sp_exequtesql`. – Thom A Apr 20 '23 at 15:58
  • 1
    *"Any ideas how to cope with this?"* You *could* define a table type and then pass than as a `READONLY` parameter, though I agree with @SeanLange that this sounds like a design going in the wrong direction. Most likely an [XY Problem](//xyproblem.info). – Thom A Apr 20 '23 at 15:59
  • 4
    You can read it, but not manipulate it. `SELECT * INTO #t FROM inserted; EXEC sys.sp_executesql N'SELECT * FROM #t;';` – Aaron Bertrand Apr 20 '23 at 16:02
  • @Stu It's the same SPID https://dbfiddle.uk/vmLF7mGa I don't think they are in tempdb, they are memory tables not table variables. https://dbfiddle.uk/26_at9rb – Charlieface Apr 20 '23 at 16:20
  • If you show your trigger there may be another way of handling it, such as with JSON or XML. – Charlieface Apr 20 '23 at 16:21
  • @Charlieface ok thanks for clarifying that - admittedly made an assumption. Pretty sure they are backed by TempDB, they would have to be, if you update a billion row table the inserted/deleted data needs to be preserved somewhere if there's not enough available space in ram... – Stu Apr 20 '23 at 16:27
  • @Stu Possibly, but I think by default they are only cached in memory where possible. This is different from table variables, where they are always in `tempdb` (which itself has its own memory caching). – Charlieface Apr 20 '23 at 16:28
  • 1
    The "inserted" and "deleted" tables used to be just logical "tables" that were in reality backed by reading the transaction log. Since SQL Server 2005 version store is used rather than the transaction log – Martin Smith Apr 20 '23 at 16:30
  • I have a question to @OP, what's the point of these questions? Aren't you supposed to figure it out by yourself if you're sitting in an interview? Or are you preparing for one? – siggemannen Apr 20 '23 at 16:39
  • @MartinSmith Is that version store is stored in tempdb? Is that the same as the version store used for RCSI? – Charlieface Apr 20 '23 at 16:53
  • @Charlieface - yes. You can query `sys.dm_tran_version_store` to see the records (binary format) - if you have an enabled trigger it can end up storing rows in there purely for the trigger even if snapshot isolation not enabled. e.g. see https://stackoverflow.com/a/47661502/73226 – Martin Smith Apr 20 '23 at 16:55

0 Answers0