Advertisement

04.09.2008 at 01:08PM PDT, ID: 23309564
[x]
Attachment Details

Creating a Before Insert Trigger

Asked by ckangas7 in SQL Server 2005

Tags: SQL 2005, 2005

Am trying to convert an oracle table over to sql 2005.  In Oracle this table had a before trigger on it so that when a record was insterted it would insert the next highest value into the primary key field "person_id".  I recreated the trigger with the below code, but it will not work as I can only create the trigger as an AFTER Insert event.  Is there any way to make it a BEFORE trigger like in Oracle?Start Free Trial 
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:

USE [episuite]
GO
/****** Object:  Trigger [EPI_PERSON_INSERT]    Script Date: 04/09/2008 13:07:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
-- =============================================
-- Author:		Charlie 
-- Create date: 
-- Description:	
-- =============================================
CREATE TRIGGER [EPI_PERSON_INSERT] 
   ON  [dbo].[EPI_PERSON] 
   FOR INSERT
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
 
    declare
		@seq_val varchar(10)
select @seq_val = Max(CAST (person_id AS NUMERIC (10,0))) + 1 from EPI_PERSON where isnumeric(person_id)=1 
Insert into EPI_PERSON (Person_id)
Values(@seq_val)
 
END
[+][-]04.09.2008 at 01:15PM PDT, ID: 21318612

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]04.09.2008 at 01:29PM PDT, ID: 21318753

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]04.09.2008 at 01:33PM PDT, ID: 21318786

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]04.09.2008 at 02:23PM PDT, ID: 21319209

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]04.10.2008 at 12:54AM PDT, ID: 21322559

View this solution now by starting your 7-day free trial. Setting up your free trial is quick, easy, and secure. We will return you to this solution, unlocked, when you're done.

 

About this solution

Zone: SQL Server 2005
Tags: SQL 2005, 2005
Sign Up Now!
Solution Provided By: angelIII
Participating Experts: 3
Solution Grade: A
 
 
[+][-]04.11.2008 at 12:42PM PDT, ID: 21337521

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
 
Loading Advertisement...
20080716-EE-VQP-32 / EE_QW_2_20070628