I need to know the query for this trigger in Microsoft SQL management server. I
ID: 3753685 • Letter: I
Question
I need to know the query for this trigger in Microsoft SQL management server. I need it to be specific so I can copy and paste for the first column and follow the pattern for the others. I need it to be specific please not general. The general stuff confuses me and doesn't help because I don't know what to change. Thanks. The purpose of this assignment is to alter and apply the trigger code.
The purpose of this assignment is to alter and apply the trigger code.
Apply the trigger code provided below to each column of the "Person.Person" table to track the number of modifications of data for each column in the table.
Trigger Code:
CREATE TRIGGER [TRIGGER_ALTER_COUNT] ON [dbo].[tblTriggerExample]
FOR INSERT, UPDATE
AS
BEGIN
DECLARE @TransID VARCHAR(36)
SELECT @TransID = TransactionID FROM INSERTED
UPDATE [dbo].[tblTriggerExample] SET AlterCount = AlterCount + 1
,LastUpdate = GETDATE()
WHERE TransactionID = @TransID
END
Table - Person.Person Columns - BusinessEntityID, PersonType, NameStyle, Title, FirstName, [MiddleName],[LastName], [Suffix], [EmailPromotion], [AdditionalContactInfo], [Demographics], [rowguid], [ModifiedDate]
Explanation / Answer
UPDATE() function may not a reliable way to determine whether the value changed for a column, since it will always return true if the column is included in the SET section in UPDATE operation, even if the value is the same as original. So you may consider to comparing the new and old values based on INSERTED and DELETED tables within the trigger.
Based on my test, it seems using UPDATE() function with variables as parameter is not supported. In this case, you may need to specify each column one by one.
here's the code:
CREATE TRIGGER [TRIGGER_ALTER_COUNT] ON [dbo].[tblTriggerExample]
FOR INSERT, UPDATE
AS
BEGIN
DECLARE @TransID VARCHAR(36)
DECLARE @StringColumn nvarchar (50)
DECLARE @Counter int
DECLARE @ColumnCount int
SELECT @ColumnCount =(SELECT COUNT(COLUMN_NAME)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'person')
WHILE @Counter < @ColumnCount
BEGIN
Set @Counter = @Counter + 1
SELECT @StringColumn = (SELECT COL_NAME(OBJECT_ID(@TableName), @Counter) AS 'Column_Name')
If Update(@StringColumn)
IF UPDATE(SampleName)
BEGIN
UPDATE tblSample SET
SampleNameLastChangedDateTime = CURRENT_TIMESTAMP
WHERE
SampleID IN (SELECT Inserted.SampleID
FROM Inserted LEFT JOIN Deleted ON Inserted.SampleID = Deleted.SampleID
WHERE
COALESCE(Inserted.SampleName, '') <> COALESCE(Deleted.SampleName, ''))
--If the value changed, do the updae
END
END