Hi synergeticsoul!
No need to create a trigger. You just simply check if a certain record doesn't exists then proceed with inserting it.
Simply do it this way :
:: REVISED PROCEDURE ::
CREATE PROCEDURE INSERT_Alumni
(
@arg_fname VARCHAR (50),
@arg_lname VARCHAR (50),
@arg_address VARCHAR (200),
@arg_city VARCHAR (50),
@arg_state VARCHAR (50),
@arg_zip NUMERIC,
@arg_phone NUMERIC,
@arg_email VARCHAR (100),
@arg_year1 NUMERIC,
@arg_year2 NUMERIC,
@arg_birthyear NUMERIC,
@AlumniID INT OUT
)
AS
BEGIN
-- If record doesn't exists then proceed with inserting
IF NOT EXISTS (Select * From PAlumni Where fname = @arg_fname And lname = @arg_lname And email = @arg_email)
BEGIN
INSERT INTO PAlumni (fname, lname, address, city, state, zip, phone, email, year1, year2, birthyear, submitDate)
VALUES (@arg_fname, @arg_lname, @arg_address, @arg_city, @arg_state, @arg_zip, @arg_phone, @arg_email, @arg_year1, @arg_year2, @arg_birthyear, GETDATE())
END
ELSE
-- No insertion will be process since it's already existing in the table
END
GO
And by the way, I just have a correction on your procedure. You missed the parameter "@arg_state" to be passed during insertion. Kindly double-check it.
Hope this helps you. Just try it.
Goodluck!
eNTRANCE2002 :-)
Main Topics
Browse All Topics
by: trailblazzyr55Posted on 2005-06-09 at 16:59:34ID: 14184821
here try this, change the primarykeyfield to whatever your ID field is... this is the idea though ;o)
CREATE PROCEDURE INSERT_Alumni
(
@arg_fname VARCHAR (50),
@arg_lname VARCHAR (50),
@arg_address VARCHAR (200),
@arg_city VARCHAR (50),
@arg_state VARCHAR (50),
@arg_zip NUMERIC,
@arg_phone NUMERIC,
@arg_email VARCHAR (100),
@arg_year1 NUMERIC,
@arg_year2 NUMERIC,
@arg_birthyear NUMERIC,
@AlumniID INT OUT
)
AS
DECLARE @PrimaryKey int
SELECT @PrimaryKey = PrimaryKeyField
FROM PAlumni
WHERE fname = @arg_fname AND
lname = @arg_lname AND
submitDate != GETDATE()
/* Do whatever you need to above in order to make this guarantee that it was a duplicate record */
IF @PrimaryKey IS NULL
BEGIN
INSERT INTO PAlumni
(fname, lname, address, city, state, zip, phone, email, year1, year2, birthyear, submitDate)
VALUES
(@arg_fname, @arg_lname, @arg_address, @arg_city, @arg_state, @arg_zip, @arg_phone, @arg_email, @arg_year1, @arg_year2, @arg_birthyear, GETDATE())
SELECT @PrimaryKey=@@IDENTITY
END
GO