March 14, 2016 at 12:22 pm
using UPDATED and DELETED temp tables, how can I override the new value and keep the old value for a column using UPDATE trigger. My requirement is not to give user an error, but keep the old value for just one column.
Anyone can provide a script would be highly appreciated.
many thanks!
March 14, 2016 at 12:41 pm
SQL!$@w$0ME (3/14/2016)
using UPDATED and DELETED temp tables, how can I override the new value and keep the old value for a column using UPDATE trigger. My requirement is not to give user an error, but keep the old value for just one column.Anyone can provide a script would be highly appreciated.
many thanks!
You could write an INSTEAD OF UPDATE trigger to do whatever you want to the table when an UPDATE is attempted. Or an AFTER UPDATE trigger to set the column back to how it was before the update.
March 14, 2016 at 1:05 pm
Thanks Phil. Can I accomplish this with just one update trigger?
Can you give me a logic for the update trigger. The script below is not working as intended.
--Column name(use old value) - Column1
IF EXISTS (SELECT * FROM DELETED)
BEGIN
DECLARE @Column1Old VARCHAR(35)
select @Column1Old =Column1 from deleted;
UPDATE dbo.[Table1]
SET Column1 =@Column1Old
WHERE EXISTS
(SELECT *
FROM INSERTED AS i
WHERE i.ID = Table1.ID);
END
March 14, 2016 at 1:14 pm
what is someone is updating the value from blank/null to a desired value, instead of just changing it? is it ok to update from blank to good value? or prevent changes no matter what?
what if someone were to delete the row and put it back? do you have to take that into account? (ie a developer cannot update the row, but can delete it and put it back the way he wants it to look...) then you need an on delete trigger too!
here's an instead of trigger
IF OBJECT_ID('[dbo].[TR_TargetTable]') IS NOT NULL
DROP TRIGGER [dbo].[TR_TargetTable]
GO
--UPDATE TargetTable SET Location = NULL WHERE TargetTableID IN(349,350)
--SELECT * FROM TargetTable WHERE TargetTableID IN(349,350)
--#################################################################################################
--Trigger added 06/09/2015 LI
--users via interface are accidentally removing the Location value, which directly affects mapping of info to DWH
--Absolutely must prevent users form modifying this value.
--the Location Id cannot be allowed to be set to null or zero.
--#################################################################################################
CREATE TRIGGER TR_TargetTable ON TargetTable
INSTEAD OF UPDATE
AS
BEGIN
SET NOCOUNT ON;
UPDATE MyTarget
SET MyTarget.[InstitutionID] = MySource.[InstitutionID],
--the protected string value: don't let changes occur unless chagning from blank to new value.
MyTarget.[DatasetValue] = CASE
WHEN LTRIM(RTRIM(ISNULL(MySource.[DatasetValue],''))) = ''
THEN MyOldSource.[DatasetValue]
WHEN LTRIM(RTRIM(ISNULL(MySource.[DatasetValue],''))) <> LTRIM(RTRIM(ISNULL(MyOldSource.[DatasetValue],'')))
AND MyOldSource.[DatasetValue] <> ''
THEN MyOldSource.[DatasetValue]
ELSE MySource.[DatasetValue]
END,
MyTarget.[CreatedDate] = MySource.[CreatedDate],
MyTarget.[CreatedBy] = MySource.[CreatedBy],
MyTarget.[UpdatedDate] = MySource.[UpdatedDate],
MyTarget.[UpdatedBy] = MySource.[UpdatedBy],
MyTarget.[Deleted] = MySource.[Deleted],
MyTarget.[DeletedDate] = MySource.[DeletedDate],
MyTarget.[DeletedBy] = MySource.[DeletedBy],
--the protected integer column: allow changes form blank to new value, but not a value to another value, or value to blank
MyTarget.[Location] = CASE
WHEN ISNULL(MyOldSource.[Location],0) <> 0
AND ISNULL(MySource.[Location],0) = 0
THEN MyOldSource.[Location]
ELSE MySource.[Location]
END
FROM TargetTable MyTarget
INNER JOIN INSERTED MySource
ON MyTarget.TargetTableID = MySource.TargetTableID
INNER JOIN DELETED MyOldSource
ON MyTarget.TargetTableID = MyOldSource.TargetTableID
END
GO
IF OBJECT_ID('[dbo].[TR_TargetTable_Delete]') IS NOT NULL
DROP TRIGGER [dbo].[TR_TargetTable_Delete]
GO
--#################################################################################################
--Trigger added 06/09/2015 LI
--users via interface are accidentally removing the Location value, which directly affects mapping of info to DWH
--Absolutely must prevent users form modifying this value.
--the Location Id cannot be allowed to be set to null or zero.
--#################################################################################################
CREATE TRIGGER TR_TargetTable_Delete ON TargetTable
INSTEAD OF DELETE
AS
BEGIN
SET NOCOUNT ON;
END
GO
Lowell
March 14, 2016 at 1:27 pm
I like Lowell's solution and I think it will perform better than an AFTER update trigger because the AFTER trigger will require 2 updates to take place while the INSTEAD OF trigger only does one update.
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
March 14, 2016 at 1:30 pm
SQL!$@w$0ME (3/14/2016)
Thanks Phil. Can I accomplish this with just one update trigger?Can you give me a logic for the update trigger. The script below is not working as intended.
--Column name(use old value) - Column1
IF EXISTS (SELECT * FROM DELETED)
BEGIN
DECLARE @Column1Old VARCHAR(35)
select @Column1Old =Column1Old from deleted;
UPDATE dbo.[Table1]
SET Column1Old =@Column1Old
WHERE EXISTS
(SELECT *
FROM INSERTED AS i
WHERE i.ID = Table1.ID);
END
A common misconception with triggers is to assume that 'deleted' and 'inserted' will contain only one row – they don't. They contain all rows which are affected by the operation in question.
Lowell has already posted an example. Here is a simpler example.
1) Set up the test table
IF Object_Id('dbo.test','U') IS NOT NULL
DROP TABLE dbo.test;
CREATE TABLE dbo.test ( Id int IDENTITY(1,1)
, Code int
, Dsc varchar(50) )
GO
CREATE TRIGGER trg1
ON dbo.test
INSTEAD OF UPDATE
AS
BEGIN
UPDATE t
SET Code = i.Code
FROM dbo.test t
join inserted i on t.Id = i.Id
JOIN deleted d ON i.Id = d.id
END
2) Sample updates do not affect the Dsc column, but they do affect the Code column:
INSERT dbo.test ( Code, Dsc )
SELECT 1
, 'Desc1'
SELECT *
FROM dbo.test
UPDATE dbo.test
SET code = 2
, Dsc = 'Cannot update'
SELECT *
FROM dbo.test
--Edit: fixed dodgy trigger join logic.
March 14, 2016 at 1:34 pm
Jack Corbett (3/14/2016)
I like Lowell's solution and I think it will perform better than an AFTER update trigger because the AFTER trigger will require 2 updates to take place while the INSTEAD OF trigger only does one update.
Agreed.
March 14, 2016 at 3:02 pm
Easiest way to accomplish this is to make the column read only when doing updates. Obviously you need to be able to enter a value when initially creating an entry. Seems like this change would be better in the application itself rather than the database.
March 14, 2016 at 4:21 pm
Thanks Lowell!
March 14, 2016 at 4:21 pm
Thanks Phil!
March 14, 2016 at 4:21 pm
Thanks Lynn!
March 14, 2016 at 5:36 pm
Few changes to requirement.
Additionally I need to update couple of columns after update for audit(user/date). Also on insert the audit columns for create user/date has to be updated. Can you please verify the script.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[TR_Table1_InsertUpdate]
ON [dbo].[Table1]
FOR INSERT, UPDATE
AS
-- Do not put anything before these lines!
IF @@ROWCOUNT = 0 RETURN;
SET NOCOUNT ON;
IF NOT EXISTS (SELECT * FROM INSERTED) RETURN;
IF EXISTS (SELECT * FROM DELETED)
BEGIN
-- Logic to handle updates goes here
print 'Updating table'
UPDATE t
SET UpdateDatetime=GETDATE(), UpdateUser=SUSER_SNAME(), CreateUser=d.CreateUser, CreateDatetime=d.CreateDatetime
FROM dbo.Table1 AS T
JOIN INSERTED i ON t.ID = i.ID
JOIN DELETED d ON i.ID=d.ID;
print 'Update completed'
END
ELSE
BEGIN
-- Logic to handle Insert goes here
Print 'Inserting to table'
UPDATE dbo.[Table1]
SET CreateDatetime=GETDATE(), CreateUser=SUSER_SNAME(),UpdateDatetime=NULL, UpdateUser=NULL
WHERE EXISTS
(SELECT *
FROM INSERTED AS i
WHERE i.ID = Table1.ID);
print 'Insert completed'
END
March 15, 2016 at 7:01 am
First thank you for writing it to handle sets, that is probably the most common thing I need to correct in triggers.
There is only one thing I would change and I put the comment in the code.
ALTER TRIGGER [dbo].[TR_Table1_InsertUpdate] ON [dbo].[Table1]
FOR INSERT, UPDATE
AS
-- Do not put anything before these lines!
IF @@ROWCOUNT = 0
RETURN;
SET NOCOUNT ON;
/* This really isn't needed because there will always
be data in the INSERTED virtual table because the trigger is
for INSERT, UPDATE */
IF NOT EXISTS ( SELECT
*
FROM
INSERTED )
RETURN;
IF EXISTS ( SELECT
*
FROM
DELETED )
BEGIN
-- Logic to handle updates goes here
PRINT 'Updating table';
UPDATE
T
SET
UpdateDatetime = GETDATE(),
UpdateUser = SUSER_SNAME(),
CreateUser = d.CreateUser,
CreateDatetime = d.CreateDatetime
FROM
dbo.Table1 AS T
JOIN INSERTED i
ON T.ID = i.ID
JOIN DELETED d
ON i.ID = d.ID;
PRINT 'Update completed';
END;
ELSE
BEGIN
-- Logic to handle Insert goes here
PRINT 'Inserting to table';
UPDATE
dbo.[Table1]
SET
CreateDatetime = GETDATE(),
CreateUser = SUSER_SNAME(),
UpdateDatetime = NULL,
UpdateUser = NULL
WHERE
EXISTS ( SELECT
*
FROM
INSERTED AS i
WHERE
i.ID = Table1.ID );
PRINT 'Insert completed';
END;
Having said that, if you are using an INSTEAD OF UPDATE trigger to make sure the "read-only" column is not updated I would put all the UPDATE logic in the INSTEAD OF UPDATE trigger and then just have an AFTER INSERT trigger that sets the CreateDateTime and CreateUser columns on INSERT.
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
March 15, 2016 at 9:17 am
Thanks a lot Jack!
March 15, 2016 at 2:17 pm
This appears to be the same code you also asked help for in this topic: http://www.sqlservercentral.com/Forums/Topic1767785-3411-1.aspx.
In the future, please keep questions on the same issue in the same topic, and only start a new topic if you have a new problem, or if the focus of the issue has changed so much that it can be considered a new problem.
@jack-2: You added a comment in the trigger code (which was originally suggested by me in that other topic) that the test on EXISTS in inserted is redundant. That is not the case. When a MERGE statement executes, you can have a trigger fire with @@ROWCOUNT > 0 but inserted empty.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy