Help for my first MS SQL Trigger

Hi all,

Recently I learned how to use Stored Procedures in MS SQL. I really like this and all it took to get me going with procedures was to get one working example and then some googling for more questions I had.

Now I would like to use my first Database Triggers and I would like to use the help of Experts Exchange to help me with that.

I would like to make sure that the field "last_modification_date" is always updated with GETDATE() whenever their is a INSERT or a UPDATE on the table.

Here is the current table:
CREATE TABLE [dbo].[SVCQuestionsForms](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[folder_name] [varchar](100) NULL,
	[description] [text] NULL,
	[last_modified_by] [varchar](100) NULL,
	[last_modified_date] [datetime] NULL,
 CONSTRAINT [PK_SVCQuestionsForms] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Open in new window


How I would build this Trigger?

Thank you
LVL 1
EtdashouAsked:
Who is Participating?
 
Jared_SCommented:
Try this out for your update...

update 
set last_modified_date = getdate()
where id = (select id from inserted)

Open in new window

or
UPDATE svcquestionsforms s
 SET last_modified_date = getdate()
 FROM svcquestionsforms s
 INNER JOIN inserted AS i
 ON s.ID = i.ID

Open in new window

0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
http://msdn.microsoft.com/en-us/library/ms189799.aspx


create trigger UpdateModifiedDate on SVCQuestionsForms
AFTER INSERT, UPDATE

update inserted set last_modification_date = getdate()
0
 
Jared_SCommented:
This might be better accomplished by giving the [last_modified_date]

default value of getdate()

or by making it a computed column.
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
EtdashouAuthor Commented:
For @Jared_S you are right for the INSERT, I should add the default value.

About the "computed column", I have no idea what this is sorry... I suppose it is really nice :|. If you have any input on how I could do this for my last_modification_date field, I would greatly appreciate. I do see that in the Table Designer I have a row named "Computed Column Specification" but I am not sure what to insert there. I did found this http://msdn.microsoft.com/en-us/library/ms191250%28v=sql.105%29.aspx but in the article it says "A computed column cannot be the target of an INSERT or UPDATE statement." and I am not sure if this would affect what I want to do.

@ged325, thank you I will try this!
0
 
Jared_SCommented:
I overlooked the update - I don't think there is a way to do that without the trigger.

"A computed column cannot be the target of an INSERT or UPDATE statement."  just means that you can't insert a value into the column or update it directly.
0
 
EtdashouAuthor Commented:
@ged325 I have tried your query. I decided to go and use the "New Triggers" option from the Triggers folder under my table.

Here is my code:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE TRIGGER [dbo].[UpdateModifiedDate]
   ON  dbo.SVCQuestionsForms
   AFTER UPDATE
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    update inserted set last_modified_date = getdate()

END
GO

Open in new window

However when I run the query I have the following error message:

Msg 286, Level 16, State 1, Procedure UpdateModifiedDate, Line 15
The logical tables INSERTED and DELETED cannot be updated.

I am not sure to understand this error.
0
 
EtdashouAuthor Commented:
@Jared_S Thank you for the explanation.
0
 
EtdashouAuthor Commented:
Hi,

Thank you Jared_S, it worked! I will assign points accordingly.

Here is my final SQL Query:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE TRIGGER [dbo].[UpdateModifiedDate]
   ON  dbo.SVCQuestionsForms
   AFTER UPDATE
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    UPDATE
		dbo.SVCQuestionsForms
    SET
		last_modified_date = getdate()
    WHERE
		id = (select id from inserted)

END
GO

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.