asked on

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

SOLUTION

Kyle Abrahams, PMP

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jared_S

This might be better accomplished by giving the [last_modified_date]

default value of getdate()

or by making it a computed column.

Etdashou

ASKER

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!

Jared_S

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.

Etdashou

ASKER

@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.

Etdashou

ASKER

@Jared_S Thank you for the explanation.

ASKER CERTIFIED SOLUTION

Jared_S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Etdashou

ASKER

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