Using a trigger to change primary key values

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Using a trigger to change primary key values

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-06-17 : 07:35:24

Katy writes "We are checking to see if an insert into our email table is being entered as a primary key or not and we have different scenerios to look for.

1. If no email values then make insert primary, and if insert is entered as primary leave it alone.
2. If they have multiple emails and they insert a non primary make it primary if they insert a primary make it (the last one entered a primary).
3. If they have multiple emails and one is primary and they are inserting a non primary then life is good, do nothing, BUT if they are inserting a primary make sure the primary is the last one entered.
4. If they have multiple emails and there are multiple primaries - and they are entering a non - primary make it a primary. Or if they already have a primary, remove all primaries and make the last one entered primary.

my variables are:
declare @lngEmailPK int, @lngStudentFK int, @lngEmailValue int, @intValue int

I am having a hard time getting a grip on this one, can you direct me to somewhere to read up on this or just let me know if this can be done properly.

Here is what I tried but I think my logic is not correct.


CREATE TRIGGER trInsertPrimaryIssue ON tblEmail
FOR INSERT
AS

declare @lngEmailPK int, @lngStudentFK int, @lngEmailValue int, @intValue int

select @lngEmailPK = lngEmailPK, @lngStudentFK = lngStudentFK, @lngEmailValue = lngEmailValue from inserted
 /*no records of email. If they are inserting make it a primary. If they inserted a primary leave alone*/

set @lngEmailValue = (select count(lngEmailValue) from tblEmail where lngEmailValue %2 = 1 and lngStudentFK = @lngStudentFK and lngEmailValue <> @lngEmailValue)

if(@lngEmailValue %2 = 0 and @intValue = 0 and @@rowcount > 0)
	--insert a non primary, make a primary
	begin
	update tblEmail
	set lngEmailValue = @lngEmailValue + 1
	where lngStudentFK = @lngStudentFK and lngEmailPK = @lngEmailPK 
	end
	else
		begin
			if(@lngEmailValue %2 = 1 and @intValue = 0 and @@rowcount = 0)
				--insert a primary, leave it alone
				begin
				update tblEmail
				set lngEmailValue = @lngEmailValue 
				where lngStudentFK = @lngStudentFK and lngEmailPK = @lngEmailPK
				end
		end
			/*if(@lngEmailValue % 2 = 0 and @intValue = 0 and @@rowcount > 0)
				--insert a non primary, make a primary
				begin
				update tblEmail
				set lngEmailValue = @lngEmailValue + 1
				end
				else
					begin*/

						if(@lngEmailValue % 2 = 1 and @intValue = 1 and @@rowcount > 0)
							--insert a primary and leave it alone
							begin
							update tblEmail
							set lngEmailValue = @lngEmailValue 
							where lngStudentFK = @lngStudentFK and lngEmailPK <> @lngEmailPK
							end
					/*end*/
							if(@lngEmailValue %2 = 0 and @intValue = 1 and @@rowcount > 0)
								--multiple emails and they already have a primary and a new non primary is inserted, do nothing
								begin
								update tblEmail
								set lngEmailValue = @lngEmailValue + 1
								where lngStudentFK = @lngStudentFK and lngEmailPK <> @lngEmailPK
								end
							else
								begin
									if(@lngEmailValue %2 = 1 and @intValue = 1 and @@rowcount > 0)
										--have a primary already and inserting a primary, make last insert the primary
										begin
										
										select @@identity
										update tblEmail
										set lngEmailValue = @lngEmailValue + 1
										where lngStudentFK = @lngStudentFK and lngEmailPK <> @lngEmailPK
										end
								end

										if(@lngEmailValue % 2 = 1 and @intValue = 1 and @@rowcount > 0)
											--multiple email, multiple primaries, remove all primaries
											begin
											update tblEmail
											set lngEmailValue = @lngEmailValue - 1
											where lngStudentFK = @lngStudentFK and lngEmailVa

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources