So you want to log the changes to a table in MS SQL Server, but you are only interested in some columns.
Writing some SQL to compare the updated and inserted tables in a TRIGGER using ORs might seem the way to go, but its really, really slow.
I found this better solution on stackoverflow and I really like the readability and speed.
This code same shows how its super quick and super easy to maintain logging on a table with a large number of columns.
Hint if you wanted to have multiple log tables e.g. one for stock information, and another for stock pricing, I would repeat the code in the trigger.
Remember to set the order your trigger execute, this needs to be the last trigger to run. As you can only have a first trigger and a last trigger specified you should limit the number of triggers on a table to 2.
CREATE TABLE stkmast_jons_log
(
log_id BIGINT IDENTITY(1, 1) PRIMARY KEY,
st_code CHAR(20),
amend_date DATE,
amend_time TIME
)
go
/****** Object: Trigger [TRIGGER_stkmast_JON_Test] Script Date: 05/04/2018 09:25:52 ******/
IF EXISTS (SELECT *
FROM sys.triggers
WHERE object_id = Object_id(N'[dbo].[TRIGGER_stkmast_JON_Test]'))
DROP TRIGGER [dbo].[TRIGGER_stkmast_JON_Test]
go
/****** Object: Trigger [dbo].[TRIGGER_stkmast_JON_Test] Script Date: 05/04/2018 09:25:52 ******/
SET ansi_nulls ON
go
SET quoted_identifier ON
go
CREATE TRIGGER [dbo].[TRIGGER_stkmast_JON_Test]
ON [dbo].[stkmast]
after UPDATE
AS
BEGIN ;
WITH src
AS (SELECT d.st_code,
d.st_bar_check,
d.st_bar_code
--,d.st_common_name_1
--,d.st_create_date
--,d.st_create_user
,
d.st_customer
--,d.st_description
--,d.st_flower_colour
,
d.st_label_cultivar
--,d.label_desc
,
d.st_label_genus
--,d.st_lg_code
--,d.st_lg_code2
--,d.marketing_column1
--,d.marketing_column2
,
d.st_override,
d.st_passport
--,d.st_peat_content
--,d.warning_text
--,d.st_pot_weight
--inherited
,
d.st_price_code,
d.st_price_type,
d.st_print_label,
d.slop_label --was d.st_bulk_notes
,
d.carecard_barcode --,d.st_qty_break_1
--,d.sales_highs --,d.st_shelves
--,d.sales_ties --,d.st_units_shelf
,
d.wholesale_price_unit,
d.retail_price_pot,
d.st_size,
d.st_size_code,
d.st_sor,
d.st_status,
d.st_stop
--,d.st_tip1 --inherited
--,d.st_tip2 --inherited
--,d.st_tip3 --inherited
--,d.st_tray_weight
,
d.st_unit_desc,
d.st_unit_qty
--,d.st_user_code
,
d.vat_code
--,d.web_stock_cat
--,d.comments
FROM deleted d
EXCEPT
SELECT d.st_code,
d.st_bar_check,
d.st_bar_code
--,d.st_common_name_1
--,d.st_create_date
--,d.st_create_user
,
d.st_customer
--,d.st_description
--,d.st_flower_colour
,
d.st_label_cultivar
--,d.label_desc
,
d.st_label_genus
--,d.st_lg_code
--,d.st_lg_code2
--,d.marketing_column1
--,d.marketing_column2
,
d.st_override,
d.st_passport
--,d.st_peat_content
--,d.warning_text
--,d.st_pot_weight
--inherited
,
d.st_price_code,
d.st_price_type,
d.st_print_label,
d.slop_label --was d.st_bulk_notes
,
d.carecard_barcode --,d.st_qty_break_1
--,d.sales_highs --,d.st_shelves
--,d.sales_ties --,d.st_units_shelf
,
d.wholesale_price_unit,
d.retail_price_pot,
d.st_size,
d.st_size_code,
d.st_sor,
d.st_status,
d.st_stop
--,d.st_tip1 --inherited
--,d.st_tip2 --inherited
--,d.st_tip3 --inherited
--,d.st_tray_weight
,
d.st_unit_desc,
d.st_unit_qty
--,d.st_user_code
,
d.vat_code
--,d.web_stock_cat
--,d.comments
FROM inserted d)
INSERT INTO dbo.stkmast_jons_log
(st_code,
amend_date,
amend_time)
SELECT i.st_code,
i.st_amend_date,
i.amend_time
FROM deleted i --log old values
INNER JOIN src d
ON i.st_code = d.st_code
END
go
TRUNCATE TABLE stkmast_jons_log
UPDATE stkmast
SET st_description = st_description
WHERE st_code LIKE 'PR%'
AND st_status = 'C'
UPDATE st
SET st_description = 'AUTUMN PATIO'-- 'AUTUMN PATIO'
FROM stkmast AS st
WHERE st_code = 'AP'
UPDATE st
SET st_Size = st_Size-- 'AUTUMN PATIO'
FROM stkmast AS st
WHERE st_code = 'AP'
UPDATE st
SET st_print_label = 'Y'-- 'AUTUMN PATIO'
FROM stkmast AS st
WHERE st_code = 'AP'
SELECT *
FROM stkmast_jons_log
go
DROP TABLE stkmast_jons_log
go
/****** Object: Trigger [TRIGGER_stkmast_JON_Test] Script Date: 05/04/2018 09:25:52 ******/
IF EXISTS (SELECT *
FROM sys.triggers
WHERE object_id = Object_id(N'[dbo].[TRIGGER_stkmast_JON_Test]'))
DROP TRIGGER [dbo].[TRIGGER_stkmast_JON_Test]
go
No comments:
Post a Comment