April 1, 2016 at 5:02 am
Hi All,
I am struggling with to create a update triggers. I have table Vijay and there are few records are available
CREATE TABLE [dbo].[vijay](
[id] [int] NULL,
[name] [varchar](20) NULL,
[Surname] [varchar](20) NULL,
[Modified_By] [varchar](20) NULL,
[Modified_DateTime] [datetime] NULL
) ON [PRIMARY]
GO
INSERT INTO [vijay](id,name, Surname)
SELECT 1, 'vijay', 'Sahu'
UNION ALL
SELECT 2, 'vinod', 'Sahu'
UNION ALL
SELECT 3, 'vikas', 'Sahu'
GO
Create TRIGGER [dbo].[vijay_UPDATE]
ON [dbo].[vijay]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
IF UPDATE(name)
BEGIN
Update vijay Set Modified_By = suser_sname(),Modified_DateTime = getdate()
END
IF UPDATE(Surname)
BEGIN
Update vijay Set Modified_By = suser_sname(),Modified_DateTime = getdate()
END
END
I have created the update trigger which will update the Modified_By and Modified_DateTime if any one
updates the Name or Surname column values.
But there is a problem if I run the below Query
Update [dbo].[vijay] SET Surname ='Sahu0000'
where id =1
[/Code]
This updates the Modified_By and Modified_DateTime for others records as well that I don't want .It should only update the Modified_By and Modified_DateTime for a record where it is updated.
Also do we need to specify indidual column names in the IF UPDATE syntax. is there any way where any of the column value is modified for a record ,It should update the Modified_By and Modified_DateTime columns.
April 1, 2016 at 5:14 am
great job posting your code and your work!
The thing about triggers, is that they have a pair of psuedo tables named INSERTED and DELETED.
Thos tables have the before and after versions of the data for the duration of the trigger.
you need to join them to the original table in order to limit yourself to just rows that changed, instead of the whole table like you are doing now.
your column [Modified_By] is limited to 20 chars, but suser_name can return 128 chars....you need to fix that or you will get an error in the trigger
i'd redo the trigger like this:
CREATE TRIGGER [dbo].[vijay_UPDATE]
ON [dbo].[vijay]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
IF UPDATE(NAME) OR UPDATE(Surname)
BEGIN
UPDATE MyTarget
SET Modified_By = Suser_sname(),
Modified_DateTime = Getdate()
FROM vijay MyTarget
INNER JOIN [inserted] MySource
ON MyTarget.ID = MySource.ID
END --IF
END --TRIGGER
Lowell
April 1, 2016 at 5:40 am
Thank you very much Lowell.
Do we need to mention every column which is being updated in IF UPDATE statement ?
Is there any way out if any column is updated then modified_by and modified_datetime should be updated a row level.
Because there might be a case where my table may have around 50 columns and if any column is updated then above mentioned columns must be updated.
According to solution I need to mention all 50 columns in that case.
Please advice.
April 1, 2016 at 5:52 am
If you want the trigger to update those columns regardless of which columns are updated, just remove the IF clause altogether.
John
April 1, 2016 at 5:53 am
vijay_uitrgpv (4/1/2016)
Thank you very much Lowell.Do we need to mention every column which is being updated in IF UPDATE statement ?
Is there any way out if any column is updated then modified_by and modified_datetime should be updated a row level.
Because there might be a case where my table may have around 50 columns and if any column is updated then above mentioned columns must be updated.
According to solution I need to mention all 50 columns in that case.
Please advice.
the trigger as designed, will only update if two specific columns modify. In practical terms,you might use that if you only care about changes to specific columns, but ignore changes in other columns.
by simply removing the If test, and if ANY update occurs, the modified date will be populated.
this is a common strategy, where a DateCreated column gets populated by a default, but a DateModified column is handled by a trigger; that way you know the Date the record was created, as well as last modified.
CREATE TRIGGER [dbo].[vijay_UPDATE]
ON [dbo].[vijay]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
UPDATE MyTarget
SET Modified_By = Suser_sname(),
Modified_DateTime = Getdate()
FROM vijay MyTarget
INNER JOIN [inserted] MySource
ON MyTarget.ID = MySource.ID
END --TRIGGER
IF OBJECT_ID('[dbo].[vijay]') IS NOT NULL
DROP TABLE [dbo].[vijay]
GO
CREATE TABLE [dbo].[vijay] (
[id] INT NULL,
[name] VARCHAR(20) NULL,
[Surname] VARCHAR(20) NULL,
[Created_By] VARCHAR(128) NULL CONSTRAINT [DF__vijay__Created_B__1A016BAA] DEFAULT (suser_sname()),
[Created_Datetime] DATETIME NULL CONSTRAINT [DF__vijay__Created_D__1AF58FE3] DEFAULT (getdate()),
[Modified_By] VARCHAR(128) NULL,
[Modified_DateTime] DATETIME NULL)
Lowell
April 1, 2016 at 6:04 am
If all columns are "important" for updating your who/when columns then you don't need to check for them. You simply update the fields joining to INSERTED without a WHERE clause. So ANY update gets your two fields updated. You only need to check fields updated if you need some exceptional handling that doesn't involve any/all fields.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply