November 9, 2015 at 6:07 am
We don't like triggers.
(Is this true ?).
Now I have to create on an existing system a history table on a table called Ben.
So my setup would be:
1. Rename Ben into TBen.
2. CREATE VIEW Ben AS SELECT * FROM TBen
3. CREATE a table Ben_history.
4. Create an instead of trigger, which copies the old information into Ben_history and
then mutates Tben.
Advantages of this method would be.
1. Old code can remain as it is and still the functionality is added.
2. Maintenance can be done on the Base table were the trigger is not fired.
Is this a feasable solution or should I avoid a solution like this?
An update with a table, becomes removal of all 'deleted' rows and an insert of all 'inserted' rows, this becomes complex if there is an identity involved. And this could (?) hamper the performance. Is there an efficient solution for tables with an identity ?
Alternatives ?
Thanks for your time and attention,
Ben
November 9, 2015 at 8:01 am
I am preparing a script to test the above.
Problem 1:
If a default is defined in a table, and insert is done on the View, where the trigger uses the 'INSERTED' table to insert the default is not used.
Problem 2:
As above for the identity. (Not set if the insert is done via a trigger).
We don't like triggers.
Ben
My code and testing code.
With the default/identity problem.
------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
--
-- Goal audit history information of an existing table.
-- Use a view/trigger combination to achieve this.
--
-- So that the table can be maintained without firing the trigger.
-- Existing code should behave as before.
--
-- Ben Brugman
-- 20151109
--
--
drop view Ben
drop table TBen
drop table Ben_history
GO
--
-- create the existing table Ben.
--
Create table Ben
(
A varchar(300),
B int,
C datetime default(getdate()),
D varchar(300),
E int identity(1000,1)
)
-- 1 Rename Ben into TBen
exec sp_rename @objname = 'Ben' ,@newname = 'TBen' , @objtype = 'object'
select * from TBen
-- 2 Create View Ben
Go
CREATE VIEW Ben AS SELECT * FROM TBen
Go
-- 3 Create Table Ben_history
select * into Ben_history from
(
select * from Ben
union
select * from Ben
)xxx where 1 = 2
-- Testing/debuggin code --------------------------------------------------------------------------------------
--
-- For testing create a ##D table and a ##I table to hold the 'deleted' and 'inserted' tables.
--
------ exec sp_drop ##D -- Deletes ##D if exists
------ exec sp_drop ##I -- Deletes ##I if exists
select * into ##D from Ben_history where 1 = 2
select * into ##I from Ben_history where 1 = 2
-- Testing/debuggin code end end end---------------------------------------------------------------------------
-- 4 Create a trigger.
go
CREATE TRIGGER [dbo].[Ben_Trigger]
ON [dbo].[Ben]
instead of update,insert,delete
AS
-- =============================================
-- Author:Ben Brugman
-- Create date: 20151109
-- Description:test trigger view
-- =============================================
-- The View Ben should behave like the previous table Ben (now TBen).
-- Changes should be stored in the table Ben_history.
--
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Testing/debuggin code --------------------------------------------------------------------------------------
insert into ##D select * from deleted
insert into ##I select * from inserted
-- Testing/debuggin code end end end---------------------------------------------------------------------------
insert into Ben_history select * from deleted
delete from TBen where E in (select E from deleted)
SET IDENTITY_INSERT TBen ON
insert into TBen (a,b,c,d,e) select A,B,C,D,E from inserted
SET IDENTITY_INSERT TBen OFF
-- Insert statements for trigger here
END
GO
------------------------------------------------------------------------------------------------------------
-- TEST SCRIPT.
------------------------------------------------------------------------------------------------------------
IF 1 = 2 BEGIN
-- This section contains some test statements.
-- Show content.
--
delete Ben
delete Ben_history
delete ##D
delete ##I
--
-- Defaults and identities do work on the table.
--
insert into TBen (A,B,D) values(1,2,4)
insert into TBen (A,B,D) values('A',2,'B')
insert into TBen (A,B,D) values('rood',3,'groen')
insert into TBen (A,B,D) values(71,72,74)
--
-- Defaults and identities do not work correctly when using the view/trigger combination.
--
insert into Ben (A,B,D) values(700,702,704)
insert into Ben (A,B,D) values('X',702,'Y')
insert into Ben (A,B,D) values('red',3,'green')
insert into Ben (A,B,D) values(771,772,774)
-- select 'View' X, * from Ben UNION ALL
select 'Table' , * from TBen UNION ALL
select 'history' , * from Ben_history UNION ALL
select 'Deleted' , * from ##D UNION ALL
select 'Inserted' , * from ##I
delete ##D
delete ##I
SELECT * FROM Ben where a = 'ROOD'
update Ben set D = 'COLOUR' where a = 'ROOD'
-- select 'View' X, * from Ben UNION ALL
select 'Table' , * from TBen UNION ALL
select 'history' , * from Ben_history UNION ALL
select 'Deleted' , * from ##D UNION ALL
select 'Inserted' , * from ##I
delete Ben where A in ('a')
-- select 'View' X, * from Ben UNION ALL
select 'Table' , * from TBen UNION ALL
select 'history' , * from Ben_history UNION ALL
select 'Deleted' , * from ##D UNION ALL
select 'Inserted' , * from ##I
END -- IF 1 = 2
November 9, 2015 at 11:33 am
I don't have a problem with triggers when designed and coded properly. For something like logging a change history I think triggers are the best way to do it.
I'm not sure why you need to rename the existing table in order to create the history table. I'd just leave the existing table and create a new table called ben_history and create an trigger on ben for UPDATE, DELETE that puts the data from the DELETED virtual table into ben_history.
So the code would be something like this:
CREATE TABLE Ben
(
A VARCHAR(300),
B INT,
C DATETIME DEFAULT (GETDATE()),
D VARCHAR(300),
E INT IDENTITY(1000, 1)
);
GO
CREATE TABLE Ben_History
(
A VARCHAR(300),
B INT,
C DATETIME DEFAULT (GETDATE()),
D VARCHAR(300),
E INT IDENTITY(1000, 1),
modification_type CHAR(1) CONSTRAINT CK_ben_history_modification_type CHECK (modification_type IN ('U', 'D')),
modified_date DATETIME NOT NULL
CONSTRAINT DF_ben_history_modified_date DEFAULT GETDATE(),
modified_by VARCHAR(128) NOT NULL
CONSTRAINT DF_ben_history_modified_by DEFAULT SYSTEM_USER
);
GO
CREATE TRIGGER Ben_upd_del ON dbo.Ben
AFTER UPDATE, DELETE
AS
BEGIN;
SET NOCOUNT ON;
INSERT INTO dbo.Ben_History
(
A,
B,
C,
D,
E,
modification_type
)
SELECT
D.A,
D.B,
D.C,
D.D,
D.E,
CASE WHEN I.E IS NULL THEN 'D'
ELSE 'U'
END AS modification_type
FROM
Deleted AS D
LEFT JOIN Inserted AS I
ON D.E = I.E;
END;
GO
/* if you need a view to show all including history */
CREATE VIEW ben_all
AS
SELECT
B.A,
B.B,
B.C,
B.D,
B.E,
NULL AS modification_type,
NULL AS modifed_date,
NULL AS modified_by,
'Current Row' AS row_type
FROM
dbo.Ben AS B
UNION ALL
SELECT
BH.A,
BH.B,
BH.C,
BH.D,
BH.E,
BH.modification_type,
BH.modified_date,
BH.modified_by,
'history row' row_type
FROM
dbo.Ben_History AS BH;
I don't log inserts because if there aren't any modifications then there isn't a history, just a current row.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 9, 2015 at 11:43 am
Or you could upgrade to SQL 2016 and use a TEMPORAL table. No triggers necessary. 🙂
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 10, 2015 at 1:52 am
Thank you Jack and Gail,
Jack Corbett (11/9/2015)
I'm not sure why you need to rename the existing table in order to create the history table. I'd just leave the existing table and create a new table called ben_history and create an trigger on ben for UPDATE, DELETE that puts the data from the DELETED virtual table into ben_history.
We like to keep the possibility to access the table without firing the triggers. Putting the trigger on the view for normal mutations the trigger(s) will fire. Using the table directly does not fire the trigger.
To go one step further.
Concept, never tried it in a production database.
Hide tables (especially large ones) behind a view.
This gives the possibility to make changes to the table without the needed changes to the code.
One example would be a time consuming change to a table without going offline. Behind the view you can make the transformation over time, while the view keeps acting as the real table.
I allready indicated that we are at least a bit apprehensive to use triggers, it makes the system more complex, harder to test, and bugs more difficult to find because of the extra layer. Testing the theory I allready noticed that, for defaults and for identities; provisions have to be made within the trigger. So thank you for the guidance, which is much appreciated.
GilaMonster (11/9/2015)
Or you could upgrade to SQL 2016 and use a TEMPORAL table. No triggers necessary. 🙂
Thanks for the advise, I have to look into SQL 2016, but for most (all) systems we are still far removed from SQL 2016. So at this moment in time this is not going to provide the solution we are looking for.
Thanks for your suggestions,
Ben
November 10, 2015 at 8:31 am
ben.brugman (11/10/2015)
Thank you Jack and Gail,Jack Corbett (11/9/2015)
I'm not sure why you need to rename the existing table in order to create the history table. I'd just leave the existing table and create a new table called ben_history and create an trigger on ben for UPDATE, DELETE that puts the data from the DELETED virtual table into ben_history.We like to keep the possibility to access the table without firing the triggers. Putting the trigger on the view for normal mutations the trigger(s) will fire. Using the table directly does not fire the trigger.
Doesn't this defeat the purpose of having a trigger to track changes? If I were an auditor I'd certainly question the validity of the change history with this design.
To go one step further.
Concept, never tried it in a production database.
Hide tables (especially large ones) behind a view.
This gives the possibility to make changes to the table without the needed changes to the code.
One example would be a time consuming change to a table without going offline. Behind the view you can make the transformation over time, while the view keeps acting as the real table.
Making changes to the table would still block use of the view while Schema Modification locks are taken, so you'd still likely need some offline time to make changes. It would certainly block any data modifications since the trigger(s) would need to be updated handle the changes to the base table.
You'd also probably want to create the view with SCHEMABINDING so table changes don't break the view.
I allready indicated that we are at least a bit apprehensive to use triggers, it makes the system more complex, harder to test, and bugs more difficult to find because of the extra layer. Testing the theory I allready noticed that, for defaults and for identities; provisions have to be made within the trigger. So thank you for the guidance, which is much appreciated.
Triggers are a great tool for audit/history tables especially when written in a set-based manner. I agree that they can make troubleshooting a bit more difficult, but when you add a view with INSTEAD OF triggers on top of a base table, you've added another couple of layers of complexity to the troubleshooting process, particularly because you have to make sure the code in the INSTEAD OF triggers does the complete duplication of the command and logs the history. An AFTER trigger on the base table only adds one layer and only has to do the insert into the history table.
Identity and defaults are only an issue when using INSTEAD OF triggers. When using the default AFTER trigger, the trigger fires after identity & default values are generated, so the code I listed doesn't have any issues with columns with these properties/constraints.
GilaMonster (11/9/2015)
Or you could upgrade to SQL 2016 and use a TEMPORAL table. No triggers necessary. 🙂Thanks for the advise, I have to look into SQL 2016, but for most (all) systems we are still far removed from SQL 2016. So at this moment in time this is not going to provide the solution we are looking for.
Thanks for your suggestions,
Ben
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 10, 2015 at 11:09 am
Jack Corbett (11/10/2015)
Doesn't this defeat the purpose of having a trigger to track changes? If I were an auditor I'd certainly question the validity of the change history with this design.
For the larger maintenance actions we do not want a change history. For example the end of year batch processing would generate to much data in the history table.
Making changes to the table would still block use of the view while Schema Modification locks are taken, so you'd still likely need some offline time to make changes. It would certainly block any data modifications since the trigger(s) would need to be updated handle the changes to the base table.
Renaming a table and implementing a trigger works fast enough. Our end users are humans and can wait for the seconds this takes.
For larger transformation, for example for changing a partioned heap table in a not partitioned clustered table, I think this technique can be used.
(Remark another usage than for the history table).
1. Rename the old table.
2. Create a new table with the new design.
3. Make a view with a 'Union' on both tables. (Selects of the columns must match offcource).
4. Make a trigger which does the appropriate actions:
insert is an insert to the new table
update depends on were the row is. remove and insert or an update.
(For sets remove from old, update existing in new table, insert new in new table).
delete is a delete from the table were the row is. (For sets delete from both tables).
5. Now touch all rows with an update, do this over some time. (hours, days, weeks).
6. When ready adjust the view.
Yes, this is something like changing the sparewheel on a moving car, but not impossible.
Might have a performance impact, but you can keep driving.
Triggers are a great tool for audit/history tables especially when written in a set-based manner. I agree that they can make troubleshooting a bit more difficult, but when you add a view with INSTEAD OF triggers on top of a base table, you've added another couple of layers of complexity to the troubleshooting process, particularly because you have to make sure the code in the INSTEAD OF triggers does the complete duplication of the command and logs the history. An AFTER trigger on the base table only adds one layer and only has to do the insert into the history table.
As said sometimes you want to do 'large' maintenance actions where triggers are not welcome. But yes AFTER triggers cause far les problems.
Identity and defaults are only an issue when using INSTEAD OF triggers. When using the default AFTER trigger, the trigger fires after identity & default values are generated, so the code I listed doesn't have any issues with columns with these properties/constraints.
Yes the defaults and Identities are not (completely) automatic with INSTEAD OF triggers. For defaults this can be mended, but you can not insert a NULL, were on a BASE TABLE you can.
For identities, they are 0 in the inserted table for new rows so this can be mended on insert. (Do not insert the identity when it is 0). If it is not 0, you can insert (or update) the row. This gives the slight advantage that it is easier to insert a identity in the view. (Within the trigger a INSERT ON is needed).
As said it's like changing a wheel on a driving car.
(Do not do this at home)
Possible.
Why this 'weird' construction ? Management !
After a proposal for an 'improvement', their requirements went up. So a 'No Down' time was added.
So I came up with a construction were there was no down time for a rather large transformation in the database.
The above is only a small part of the actual problem.
Thanks for your response.
Ben
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply