Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Help for my first MS SQL Trigger

Posted on 2012-09-13
8
Medium Priority
?
426 Views
Last Modified: 2012-09-13
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
0
Comment
Question by:Etdashou
  • 4
  • 3
8 Comments
 
LVL 41

Assisted Solution

by:Kyle Abrahams
Kyle Abrahams earned 300 total points
ID: 38394954
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
 
LVL 12

Expert Comment

by:Jared_S
ID: 38394971
This might be better accomplished by giving the [last_modified_date]

default value of getdate()

or by making it a computed column.
0
 
LVL 1

Author Comment

by:Etdashou
ID: 38395111
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 12

Expert Comment

by:Jared_S
ID: 38395234
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
 
LVL 1

Author Comment

by:Etdashou
ID: 38395242
@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
 
LVL 1

Author Comment

by:Etdashou
ID: 38395277
@Jared_S Thank you for the explanation.
0
 
LVL 12

Accepted Solution

by:
Jared_S earned 700 total points
ID: 38395642
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
 
LVL 1

Author Comment

by:Etdashou
ID: 38395873
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

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question