in Education by
I posted a question about my Dynamic Trigger yesterday and it turns out my last issue was due to including one too many fields in my unpivot. That being said, my creation is working! (evil laugh) A lot of you expressed your concerns that this could be bad for the overall health of my database and I am curious why that would be... Please elaborate after reviewing what I have come up with. This is a trigger that can be placed on ANY TABLE on ANY SERVER (once you have the necessary things in place). You either need to follow the model below or tweak it to your liking. (i.e. your own log table, your own way of tracking who made a change, etc.) All you need: The trigger, a logging table (see mine below), and a field in your tables that tracks the user that made the change (in our case all connections go through one centralized SQL profile so this is the only way we can track which user made the change (by passing it as a parameter at the time of update)). tblChangeLog: ID - int - NOT NULL (Identitity (1, 1)) Message - varchar(max) - NOT NULL TableName - varchar(50) - NOT NULL PrimaryKey - varchar(50) - NOT NULL Activity - varchar(50) - NOT NULL CreatedByUser - varchar(30) - NOT NULL - DEFAULT ('System') CreatedDate - datetime - NOT NULL - DEFAULT (getdate()) Trigger: (Haven't added delete/insert yet, may only do this for updates)... CREATE TRIGGER (your name here) ON (your table here) AFTER UPDATE AS BEGIN SET NOCOUNT ON; DECLARE @tableName sysname DECLARE @tableId INT DECLARE @activity VARCHAR(50) DECLARE @sql nvarchar(MAX) -- DETECT AN UPDATE (Records present in both inserted and deleted) IF EXISTS(SELECT * FROM inserted) AND EXISTS(SELECT * FROM deleted) BEGIN SET @activity = 'UPDATE' -- Gets TableName and TableId SELECT @tableName = OBJECT_NAME(parent_object_id) , @tableId = parent_object_id FROM sys.objects WHERE sys.objects.name = OBJECT_NAME(@@PROCID) -- Get the user who made the change DECLARE @LastUpdUser VARCHAR(50) SELECT @LastUpdUser = LastUpdUser FROM inserted -- Stores possible column names CREATE TABLE #Columns ( name varchar(100) ) -- Stores only updated columns CREATE TABLE #Changes ( Id sql_variant, FieldName sysname, FieldValue_OLD sql_variant, FieldValue_NEW sql_variant, DateChanged datetime DEFAULT (GETDATE()), LastUpdUser varchar(50), GroupNumber int ) -- Gathers names of all possible updated columns (excluding generic) INSERT INTO #columns SELECT Name FROM sys.columns WHERE object_id = @tableId AND Name NOT IN ('LastUpdUser', 'LastUpdDate', 'CreatedByUser', 'CreatedDate', 'ConcurrencyId') -- Builds 2 dynamic strings of columns to use in pivot DECLARE @castFields nvarchar(max) -- List of columns being cast to sql_variant DECLARE @listOfFields nvarchar(max) -- List of columns for unpivot SELECT @castFields = COALESCE(@castFields + ', ', '') + ('CAST(' + QUOTENAME(Name) + ' AS sql_variant) [' + Name + ']') FROM #columns SELECT @listOfFields = COALESCE(@listOfFields + ', ', '') + QUOTENAME(Name) FROM #columns WHERE Name <> 'Id' -- Inserting deleted/inserted data into temp tables SELECT * into #deleted FROM deleted SELECT * into #inserted FROM inserted SELECT @sql = ';WITH unpvt_deleted AS ( SELECT Id, FieldName, FieldValue FROM (SELECT ' + @castFields + ' FROM #deleted) p UNPIVOT (FieldValue FOR FieldName IN (' + @listOfFields + ') ) AS deleted_unpivot ), unpvt_inserted AS ( SELECT Id, FieldName, FieldValue FROM (SELECT ' + @castFields + ' FROM #inserted) p UNPIVOT (FieldValue FOR FieldName IN (' + @listOfFields + ') ) AS inserted_unpivot ) INSERT INTO #Changes (Id, FieldName, FieldValue_OLD, FieldValue_NEW, LastUpdUser, GroupNumber) SELECT COALESCE(D.Id, I.Id) Id , COALESCE(D.FieldName, I.FieldName) FieldName , D.FieldValue AS FieldValue_OLD , I.FieldValue AS FieldValue_NEW , ''' + @LastUpdUser + ''' , DENSE_RANK() OVER(ORDER BY I.Id) AS GroupNumber FROM unpvt_deleted D FULL OUTER JOIN unpvt_inserted I ON D.Id = I.Id AND D.FieldName = I.FieldName WHERE D.FieldValue <> I.FieldValue DECLARE @i INT = 1 DECLARE @lastGroup INT SELECT @lastGroup = MAX(GroupNumber) FROM #Changes WHILE @i <= @lastGroup BEGIN DECLARE @Changes VARCHAR(MAX) SELECT @Changes = COALESCE(@Changes + ''; '', '''') + UPPER(CAST(FieldName AS VARCHAR)) + '': '' + '''''''' + CAST(FieldValue_OLD AS VARCHAR) + '''''' to '' + '''''''' + CAST(FieldValue_NEW AS VARCHAR) + '''''''' FROM #Changes WHERE GroupNumber = @i ORDER BY GroupNumber INSERT INTO tblChangeLog (Message, TableName, PrimaryKey, Activity, CreatedByUser, CreatedDate) SELECT Distinct @Changes, ''' + @tableName + ''', CONVERT(VARCHAR, Id), ''' + @activity + ''', LastUpdUser, DateChanged FROM #Changes WHERE GroupNumber = @i SET @Changes = NULL SET @i += 1 END DROP TABLE #Changes DROP TABLE #columns DROP TABLE #deleted DROP TABLE #inserted' exec sp_executesql @sql END END To make this even more universal, my DBA has come up with a nifty script and dummy table that will store this trigger as a row in the dummy table. The script uses a cursor and finds every table on a database and creates a trigger for each table on the fly. We have tested that as well and it works like a charm, not bothering posting that here at this time. JavaScript questions and answers, JavaScript questions pdf, JavaScript question bank, JavaScript questions and answers pdf, mcq on JavaScript pdf, JavaScript questions and solutions, JavaScript mcq Test , Interview JavaScript questions, JavaScript Questions for Interview, JavaScript MCQ (Multiple Choice Questions)

1 Answer

0 votes
by
Some things I noticed: There can be more than one record in inserted or deleted. Sql Server will call a trigger only once for a statement, even if it impacts many rows. It's possible the application is built where this is extremely unlikely, but I've seen this come back to bite people in triggers many times. Order is not preserved between when inserted and deleted are processed. I would use a parameter name with sq_executesql for the LastUpdUser. I know you're unlikely to have any problems caused by an organization-assigned username as is, but I always feel better about using parameters as much as possible, and it's possible here. The same goes for @activity. Performance. You're doing this on every single change, and it involves a considerable amount of string processing. String processing in Sql Server is surprisingly expensive, such that this is likely to create a meaningful performance penalty. Why? This ability is already built-in to Sql Server, and as of Sql Server 2016 sp2 it's included with Standard Edition (no need to get Enterprise just for this).

Related questions

0 votes
    I posted a question about my Dynamic Trigger yesterday and it turns out my last issue was due to including one ... INT SELECT @lastGroup = MAX(GroupNumber) FROM #Changes WHILE @i...
asked May 13, 2022 in Education by JackTerrance
0 votes
    can someone please explain me “this” function in brief in java and its use Select the correct answer from above options...
asked Dec 24, 2021 in Education by JackTerrance
0 votes
    Heyy mates..Am an icse student can anyone tell me any importance for java please its urgent I have my exam ... please for icse java. Select the correct answer from above options...
asked Nov 30, 2021 in Education by JackTerrance
0 votes
    When I run pip3 install -r requirements.txt on the project I get this error message: pip._vendor.pkg_resources. ... on my machine. Select the correct answer from above options...
asked Jan 19, 2022 in Education by JackTerrance
0 votes
    The code works fine but the only problem I encountered an error is: bad operand type for unary +: 'str'. This is ... for unary +: 'str' Select the correct answer from above options...
asked Jan 11, 2022 in Education by JackTerrance
0 votes
    Can you help me out with this program? I am unable to run this code. varx http = require('http'); exports.handler ... + eventa.url); } Select the correct answer from above options...
asked Jan 24, 2022 in Education by JackTerrance
0 votes
    WHY AND WHEN DO WE ADD SPACES BEFORE OR AFTER A STRING?? please explain this VERY URGENT. HELP BRAINLY MEMBERS… WILL MARK BRAINLIEST.. Select the correct answer from above options...
asked Dec 25, 2021 in Education by JackTerrance
0 votes
    Can we create Dynamic Classes in Objective C? If Yes, Explain how to create with a valid use case?...
asked Nov 10, 2020 in Technology by JackTerrance
0 votes
    The transaction can no longer continue with its normal execution because of some internal condition, such ... topic in chapter Recovery System of Database Management...
asked Oct 10, 2021 in Education by JackTerrance
0 votes
    As I tried to add a video on my top-wrapper previously. (Thank you so much for those who leave ... JavaScript Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
asked Apr 9, 2022 in Education by JackTerrance
0 votes
    hi friends hope you all are doing great Plz answer my question please do not give invalid answers Write a program ... number. Thank you Select the correct answer from above options...
asked Dec 19, 2021 in Education by JackTerrance
0 votes
    good morning everybody (can you please help me in my computer test Select the correct answer from above options...
asked Dec 6, 2021 in Education by JackTerrance
0 votes
    good morning everybody (can you please help me in my computer test Select the correct answer from above options...
asked Dec 5, 2021 in Education by JackTerrance
0 votes
    good morning everybody (can you please help me in my computer test Select the correct answer from above options...
asked Nov 26, 2021 in Education by JackTerrance
0 votes
    I have seen SQL that uses both != and for not equal. What is the preferred syntax and why? I like !=, ... reminds me of Visual Basic. Select the correct answer from above options...
asked Jan 28, 2022 in Education by JackTerrance
...