December 27, 2019 at 5:55 pm
Because of the legacy methods we already have in place, we've been slow to adopt System Versioned Temporal Tables. I have two questions that I cannot seem to find an alternate answer to and ask for your help. Admittedly, I've not actually tried the things in these questions because the documentation seems to say that these things cannot be done but wanted to ask if others may have found a trick around the problems.
Like I said, I'm pretty sure that there's no way to do either of the things above but it never hurts to ask if there's something I missed or a possible work around.
Thanks for your help, folks.
--Jeff Moden
December 27, 2019 at 6:25 pm
2. ... Current trigger technology won't even directly allow the saving of LOB columns because the LOB values don't show up in the INSERTED/DELETED logical tables in standard DML triggers. ...
?? SQL Server 2016, at least, does include LOB columns in the inserted and deleted tables (as long as they aren't one of the obsolete LOB types). Of course you don't have to reference them, but they should be there.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
December 27, 2019 at 6:37 pm
Jeff Moden wrote:2. ... Current trigger technology won't even directly allow the saving of LOB columns because the LOB values don't show up in the INSERTED/DELETED logical tables in standard DML triggers. ...
?? SQL Server 2016, at least, does include LOB columns in the inserted and deleted tables (as long as they aren't one of the obsolete LOB types). Of course you don't have to reference them, but they should be there.
Aye... thanks, Scott. The "old world" I was thinking about included the column name but not the values (and I did say "values" above... not "column names") and that may be an issue older than I remembered and, indeed, may still be a problem in the "current world" with the old LOB datatypes. Thanks for that.
Shifting gears back to the original post, any ideas other than "Nope... that's the way it is", which is also a good answer.
--Jeff Moden
December 27, 2019 at 7:16 pm
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
December 27, 2019 at 8:40 pm
For (1), you could add a column to the table itself to store the original login name there too (or, far better to save space, an id (that you assign) representing the original login). That value would obviously then be added to the temporal table as well.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
December 28, 2019 at 7:17 am
I suppose that if you don't want the LOB data in the temporal table, you could move all LOB data to an extension table with the same PK as the original table, and add a field to the original table to indicate LOB updates.
December 28, 2019 at 4:14 pm
For (1), you could add a column to the table itself to store the original login name there too (or, far better to save space, an id (that you assign) representing the original login). That value would obviously then be added to the temporal table as well.
Agreed. I do know how to add a computed column to the main table and have the Temporal Table pick up on its values. I was trying to avoid having the column in the main table (fragmentation source during ExpAnsive updates on older tables) but that appears to be impossible because of the rules of use for Temporal Tables.
The other problem is that computed columns don't work during updates and that's why I wanted the computed table to be in the History table.
Thanks for the response, Scott.
--Jeff Moden
December 28, 2019 at 4:25 pm
I suppose that if you don't want the LOB data in the temporal table, you could move all LOB data to an extension table with the same PK as the original table, and add a field to the original table to indicate LOB updates.
Thanks for the feedback. That's basically what I had resigned myself to doing. To keep from having to change a wad of code (it DOES seem to be the only way), I'll also need to rename the old table, create a view with the previous name of the table and add an instead-of trigger to the view to handle split table inserts, updates, and deletes. It's a pain but it does appear to be the only way around the LOB issue with Temporal Tables.
Thanks, again for the response.
--Jeff Moden
December 30, 2019 at 3:19 pm
I've had to use "extension" tables here a few times too, for various reasons.
If you have an identity column present, do remember / be aware of the issues with SCOPE_IDENTITY() when using an INSTEAD OF INSERT trigger.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
December 30, 2019 at 4:19 pm
I've had to use "extension" tables here a few times too, for various reasons.
If you have an identity column present, do remember / be aware of the issues with SCOPE_IDENTITY() when using an INSTEAD OF INSERT trigger.
It's been a very long time since I've used an INSTEAD OF trigger so thank you for that great reminder, Scott. Very much appreciated.
Hopefully, though, it won't be an INSTEAD OF INSERT trigger... only an INSTEAD OF UPDATE, DELETE trigger.
Now I just need to remember if I got around the fact that the INSERTED/DELETED tables are out of scope in dynamic SQL without creating temporary tables. It was so long ago, that I'm not sure that I ever did and the code I used is long gone making it a wee bit tough to verify either way.
Shifting gears a bit, I didn't care for Oracle much when I had to work with it more than a decade ago but I do remember absolutely falling in love with its real "BEFORE" triggers.
--Jeff Moden
December 30, 2019 at 4:52 pm
ScottPletcher wrote:I've had to use "extension" tables here a few times too, for various reasons.
If you have an identity column present, do remember / be aware of the issues with SCOPE_IDENTITY() when using an INSTEAD OF INSERT trigger.
It's been a very long time since I've used an INSTEAD OF trigger so thank you for that great reminder, Scott. Very much appreciated.
Hopefully, though, it won't be an INSTEAD OF INSERT trigger... only an INSTEAD OF UPDATE, DELETE trigger.
Now I just need to remember if I got around the fact that the INSERTED/DELETED tables are out of scope in dynamic SQL without creating temporary tables. It was so long ago, that I'm not sure that I ever did and the code I used is long gone making it a wee bit tough to verify either way.
Shifting gears a bit, I didn't care for Oracle much when I had to work with it more than a decade ago but I do remember absolutely falling in love with its real "BEFORE" triggers.
Don't see why you'd need dynamic code. At worst static code generated dynamically.
I guess you plan on INSERTing the LOB part of the data inline rather than relying on an INSTEAD OF trigger to do it automatically. Some extra coding everywhere, but it does avoid the SCOPE_IDENTITY() issue. Since you're on SQL 2016, you could use sp_set_session_context like we do, although for some reason the developers seem to hate it, not sure why.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
December 30, 2019 at 5:46 pm
My apologies, Scott. I wasn't clear. In this particular case, I was referring mostly to solving the problem of having a column that identifies who did the update without the double-dip and line duplication in the history table of doing an AFTER UPDATE. I wasn't even considering the LOB problem for this.
As for the "static code generated dynamically", that would be nice but, for an INSTEAD OF trigger, I don't see a way to avoid having to hit the INSERTED/DELETED logical tables because you have to decide which columns you actually want to update (based on Columns_Updated) and then use the data in the INSERTED logical table to do the actual update.
Either that, or I'm totally missing something about INSTEAD OF triggers, which is certainly a possibility.
--Jeff Moden
December 30, 2019 at 6:26 pm
I don't know, maybe I'm not thinking about it right, but I was anticipating something like below. SQL checks a column value being UPDATEd and doesn't actually change it if the value is the same, so I don't think it's that big a deal SETting every column. I think it's probably close performance-wise whether dynamic SQL is better or about the same.
However, if just one or two columns change a lot when others don't, such as status, you could put a special these-columns-only UPDATE, and only do the full UPDATE if an additional column(s) are involved.
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
CREATE TRIGGER tablename__TR_UPD
ON dbo.tablename
AFTER UPDATE
AS
SET NOCOUNT ON;
UPDATE t
SET col_name1 = CASE WHEN UPDATE(col_name1) THEN i.col_name1 ELSE t.col_name1 END,
col_name2 = CASE WHEN UPDATE(col_name2) THEN i.col_name2 ELSE t.col_name2 END,
col_name3 = [...]
FROM dbo.tablename t
INNER JOIN inserted i ON i.$IDENTITY = t.$IDENTITY
GO
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
December 31, 2019 at 1:49 am
That works great but only if that's the only UPDATE trigger on the table. Here's a test that shows that even though the value of some columns hasn't been changed, SQL Server things they have been. As you can see in the comments, that's going to be a killer for me because I will have an AFTER trigger in some cases.
I know my usage is different than what you intended but I had to try it. And, yes, I'm at starting out with the "handle all columns" method first.
--=========================================================================
-- Create and populate a test table and make both an INSTEAD OF
-- trigger and an AFTER trigger on the table.
--=========================================================================
-- DROP TABLE dbo.TriggerSource
GO
--===== Create the test table
CREATE TABLE dbo.TriggerSource
(
RowNum INT IDENTITY(1,1)
,ColA VARCHAR(20) NULL
,ColB VARCHAR(20) NULL
,ColC VARCHAR(20) NULL
CONSTRAINT PK_TriggerSource PRIMARY KEY CLUSTERED (RowNum)
)
;
GO
--===== Create the instead_of trigger to control every thing.
-- This will do the job but will have consequences on AFTER triggrers.
CREATE TRIGGER dbo.TriggerSource_INSTEAD_OF_UPDATE
ON dbo.TriggerSource
INSTEAD OF UPDATE
AS
UPDATE ts
SET ColA = CASE WHEN UPDATE(ColA) THEN i.ColA ELSE ts.ColA END
,ColB = CASE WHEN UPDATE(ColB) THEN i.ColB ELSE ts.ColB END
,ColC = CASE WHEN UPDATE(ColC) THEN i.ColC ELSE ts.ColC END
FROM dbo.TriggerSource ts
JOIN INSERTED i ON i.$IDENTITY = ts.$IDENTITY
;
GO
--===== Here's the AFTER trigger.
-- It just does a SELECT so we can see what it sees.
-- This will demonstrate that the INSTEAD OF trigger does
-- affect columns that we don't want even though the value
-- has not changed.
CREATE TRIGGER dbo.TriggerSource_AFTER_UPDATE
ON dbo.TriggerSource
AFTER UPDATE
AS
SELECT RowNum
,ColA
,ColB
,ColC
,ColA_Updated = CASE WHEN UPDATE(ColA) THEN 'Updated' ELSE '' END
,ColB_Updated = CASE WHEN UPDATE(ColB) THEN 'Updated' ELSE '' END
,ColC_Updated = CASE WHEN UPDATE(ColC) THEN 'Updated' ELSE '' END
FROM INSERTED
;
GO
--===== Add some test rows to the test table.
-- This will not fire either trigger
INSERT INTO dbo.TriggerSource
(Cola,ColB,ColC)
VALUES (1,2,3)
,(4,5,6)
,(7,8,9)
;
--=========================================================================
-- Now, let's do the test. This will prove that SQL Server updates a
-- column even if the value is unchanged and that the INSTEAD OF
-- trigger is what killed us.
--=========================================================================
--===== Let's see the original rows
SELECT * FROM dbo.TriggerSource
;
--===== Update just Column B in all rows.
-- This fires both triggers (Always INSTEAD OF first).
-- We can see that the AFTER trigger thinks all columns
-- have been updated. That's a real problem.
-- Remember that the AFTER trigger does a SELECT.
UPDATE ts
SET ColB = 'CHANGED'
FROM dbo.TriggerSource ts
;
But, I'm also being stupid because I remembered something incorrectly. The triggers that I made in the past were made to replace SQLCLR triggers... not normal AFTER triggers. The INSERTED/DELETED tables were out-of-scope for the SQLCLR triggers I replaced and they had to dump the INSERTED/DELETED tables into TempTables, which was devastating for performance when only 4 columns of a 137 column table were updated.
I wrote dynamic SQL to create replacement triggers for those and it worked great. I just can't remember if you can use dynamic SQL within a trigger to see the INSERTED/DELETED tables. If I can, this problem is solved. Guess what I'm testing next?? 😀
BTW, I appreciate the dialog here, Scott... it IS helping me to think about this problem. It's been way too long since I've worked with triggers.
--Jeff Moden
December 31, 2019 at 1:51 am
p.s. I wish they'd fix the bloody code windows for color on SSC. It would be better if they simply rendered out as all black rather than serious color mistakes they currently make.
--Jeff Moden
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply