Trigger with cursor - SQL Server Forums

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

Transact-SQL (2000)

Trigger with cursor

Author

Topic

kathy7012
Starting Member

10 Posts

Posted - 2003-11-18 : 22:20:23

I need to make a trigger for an email table. It will have a cursor and it will delete all emails that are not primary. Once all non-primary emails have been deleted it will allow the deletion of the primary key. For some reason I can't get a handle on the actual logic. Here is my code(could be way off). lngValue is the value that the member is an instructor.


CREATE Trigger trMemberEmailDelete on tblMemberEmail
for delete
as

Declare  @lngMemberEmailPK int,
	 @lngValue int,
	 @lngMemberFK int

declare crsDeleted cursor scroll

for 
select lngMemberEmailPK, strEmail, lngValue, lngMemberFK
from tblMemberEmail
where lngMemberEmailPK = @lngMemberEmailPK

open crsDeleted

fetch next
from crsDeleted

while(@@fetch_status = 0)
Begin

	--does member have a primary email, if so more than one?
	--more than one allow them to delete an email one at a time as long as one is still primary
	--loop through until they have only one primary
	--once they have one allow them to delete it
	

	if exists (Select * from tblMemberEmail where lngValue  != 1 and lngMemberFK = @lngMemberFK)
		while ((@@Fetch_Status = 0) AND (@lngValue > 1))
	 	begin
			
			delete from tblMemberEmail 
			where lngValue % 2 != 1	and lngMemberFK = @lngMemberFK
			fetch next from crsDeleted				
		end
			else 
				begin
					if exists(select * from tblMemberEmail where lngValue %2 = 1 and lngMemberFK = @lngMemberFK)
					delete from tblMemberEmail where lngValue %2 =1 and lngMemberFK = @lngMemberFK
					select lngMemberEmailPK as deletePK from deleted
				end
							
end								
								
close crsDeleted
deallocate crsDeleted

Kathy Potter

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-11-19 : 11:23:09

Kathy,

Can we back up a bit and determine exactly what you are trying to do without looking at this code/cursors/triggers/etc.... My understanding from your question is you have a table which looks something like:


create table tblMemberEmail
(
	lngMemberEmailPK int,
	strEMail varchar(50),	
	lngValue tinyint,	
	lngMemberFK int
)

and you want to delete some records from this table and another table which contains FK constraint where some criteria applies? Can you post some scripts like I provided above for the two tables and some sample data you would be working with along with you criteria.

kathy7012
Starting Member

10 Posts

Posted - 2003-11-19 : 11:36:42

create table tblMemberEmail
(
lngMemberEmailPK int(4),
lngMemberFK int(4),
strEMail varchar(50),
lngValue int(4)
)

Create table tblMember
(
lngMemberPK int (4),
strFirstName varchar(50),
strMiddleName varchar(50),
strLastName varchar(50),
lngValue int(4),
blnFullTime bit (1)

)

I am checking the email table for multiple emails and if they have multiple emails I want to delete all non-primary ones. Once all non-primary ones have been deleted I want to allow them to delete the primary email (which is not a normal scenerio). But I want them to be able to do this. So once completed, there will be no emails. It is just a confusing scenerio for me to figure out. Thank you for replying.

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-11-19 : 11:43:35

OK,

quote:

I am checking the email table for multiple emails and if they have multiple emails I want to delete all non-primary ones

Do you mean muliple strEMail for a given lngMemberEmailPK or similar strEMail in the table?

quote:

Once all non-primary ones have been deleted I want to allow them to delete the primary email (which is not a normal scenerio).

What signifies a Primary / Non-Primary Email? Is this where lngValue comes into play??

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2003-11-19 : 11:43:47

What determines which email is primary?

Sarah Berger MCSD

kathy7012
Starting Member

10 Posts

Posted - 2003-11-19 : 11:49:11

Yes, they could have a more than one email so I need to check for that, they could have home, mobile, fax, cellphone. lngMemberEmailPK will determine to which member the email is associated with and lngValue will determine if it is primary. If it is 1 it will be primary.

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-11-19 : 11:52:17

OK now were getting somewhere..

When you perform a delete, what is the criteria??

Is it something like:


DELETE FROM tblMemberEmail WHERE lngMemberEmailPK = @lngMemberEmailPK

Or something else?

kathy7012
Starting Member

10 Posts

Posted - 2003-11-19 : 11:58:14

yes, just like that exactly

kathy7012
Starting Member

10 Posts

Posted - 2003-11-19 : 12:04:58

I have referential integrity enforced and that is all.

kathy7012
Starting Member

10 Posts

Posted - 2003-11-19 : 12:05:54

Sorry, also am using SQL Server 2000

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-11-19 : 13:48:31

I may still not fully understand your need but could you just use a procedure such as:


CREATE PROCEDURE UPDeleteEmails
	@lngMemberEmailPK int,
	@Primary int=NULL
AS

DELETE 
FROM tblMemberEmail 
WHERE lngMemberEmailPK = @lngMemberEmailPK 
AND (@Primary IS NULL OR lngValue=@Primary)

GO

--This will delete all email records but the primary for a given lngMemberEmailPK = 1
EXEC UPDeleteEmails 1,0

----This will delete all email records for a given lngMemberEmailPK = 1
EXEC UPDeleteEmails 1

kathy7012
Starting Member

10 Posts

Posted - 2003-11-20 : 00:11:43

quote:

Originally posted by ehorn

I may still not fully understand your need but could you just use a procedure such as:


CREATE PROCEDURE UPDeleteEmails
	@lngMemberEmailPK int,
	@Primary int=NULL
AS

DELETE 
FROM tblMemberEmail 
WHERE lngMemberEmailPK = @lngMemberEmailPK 
AND (@Primary IS NULL OR lngValue=@Primary)

GO

--This will delete all email records but the primary for a given lngMemberEmailPK = 1
EXEC UPDeleteEmails 1,0

----This will delete all email records for a given lngMemberEmailPK = 1
EXEC UPDeleteEmails 1

kathy7012
Starting Member

10 Posts

Posted - 2003-11-20 : 00:12:38

quote:

Originally posted by kathy7012

quote:

Originally posted by ehorn

I may still not fully understand your need but could you just use a procedure such as:


CREATE PROCEDURE UPDeleteEmails
	@lngMemberEmailPK int,
	@Primary int=NULL
AS

DELETE 
FROM tblMemberEmail 
WHERE lngMemberEmailPK = @lngMemberEmailPK 
AND (@Primary IS NULL OR lngValue=@Primary)

GO

--This will delete all email records but the primary for a given lngMemberEmailPK = 1
EXEC UPDeleteEmails 1,0

----This will delete all email records for a given lngMemberEmailPK = 1
EXEC UPDeleteEmails 1

oops sent it without saying thank you!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources