Solved

Cascade Delete fails due to existing Trigger

Posted on 2009-07-09
26
516 Views
Last Modified: 2012-05-07
So I have this primary key table:
USE [UserNameStore]
GO

/****** Object:  Table [dbo].[Courses]    Script Date: 07/09/2009 14:09:46 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Courses](
      [CN] [varchar](6) NOT NULL,
 CONSTRAINT [PK_Courses_1] PRIMARY KEY CLUSTERED
(
      [CN] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Then I have 2 additional tables, which I need to cascade changes to based on deletions/updates from the Courses table. This is the DDL for those:

ENROLLMENT:


CREATE TABLE [dbo].[Enrollment](
      [PermID] [varchar](50) NOT NULL,
      [CN] [varchar](6) NOT NULL,
      [DTS] [datetime] NULL
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Enrollment]  WITH CHECK ADD  CONSTRAINT [FK_Enrollment_Courses] FOREIGN KEY([CN])
REFERENCES [dbo].[Courses] ([CN])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[Enrollment] CHECK CONSTRAINT [FK_Enrollment_Courses]
GO

ALTER TABLE [dbo].[Enrollment]  WITH CHECK ADD  CONSTRAINT [FK_Enrollment_UserStore] FOREIGN KEY([PermID])
REFERENCES [dbo].[UserStore] ([PermID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[Enrollment] CHECK CONSTRAINT [FK_Enrollment_UserStore]
GO


With the following corresponding trigger:

CREATE TRIGGER [dbo].[Enrollment_AddRemoveQueue] ON [dbo].[Enrollment]
AFTER INSERT,DELETE
 
AS
SET NOCOUNT ON;
IF EXISTS(SELECT DELETED.PermID, DELETED.CN FROM DELETED)
      INSERT INTO dbo.CourseRemoveQueue (PermID,CN) Select DELETED.PermID, DELETED.CN FROM DELETED
     
     
IF EXISTS(SELECT INSERTED.PermID FROM INSERTED)
      DELETE FROM dbo.CourseRemoveQueue
      FROM INSERTED INNER JOIN dbo.CourseRemoveQueue ON INSERTED.PermID = dbo.CourseRemoveQueue.PermID AND INSERTED.CN = dbo.CourseRemoveQueue.CN

And CourseRemoveQueue:


CREATE TABLE [dbo].[CourseRemoveQueue](
      [PermID] [varchar](50) NOT NULL,
      [CN] [varchar](6) NOT NULL,
      [DTS] [datetime] NULL
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[CourseRemoveQueue]  WITH CHECK ADD  CONSTRAINT [FK_CourseRemoveQueue_Courses] FOREIGN KEY([CN])
REFERENCES [dbo].[Courses] ([CN])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[CourseRemoveQueue] CHECK CONSTRAINT [FK_CourseRemoveQueue_Courses]
GO

ALTER TABLE [dbo].[CourseRemoveQueue]  WITH CHECK ADD  CONSTRAINT [FK_CourseRemoveQueue_UserStore] FOREIGN KEY([PermID])
REFERENCES [dbo].[UserStore] ([PermID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[CourseRemoveQueue] CHECK CONSTRAINT [FK_CourseRemoveQueue_UserStore]
GO

When I delete a row in the courses table. I get the following message:
---------------------------
Microsoft SQL Server Management Studio
---------------------------
No rows were deleted.

A problem occurred attempting to delete row 33.
Error Source: .Net SqlClient Data Provider.
Error Message: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_CourseRemoveQueue_Courses". The conflict occurred in database "UserNameStore", table "dbo.Courses", column 'CN'.

The statement has been terminated.

Correct the errors and attempt to delete the row again or press ESC to cancel the change(s).
---------------------------
OK   Help  
---------------------------

I know that for a fact that the issue is happening when the cascade delete Courses  is trying to remove the entry in CourseRemoveQueue, while the Enrollment trigger is trying to insert it, since it got deleted.

I completely understand the logic, however, I have no clue on how to deal with this. Is there some additional code I can add to the trigger to tell it to ignore that action when coming from a cascade delete somehow?

Thanks for your help!

EDIT:
I noticed as I worked more with this, that this INSERT trigger is actually error-ing out on all other tables which have a CASCADE delete property to the CourseRemoveQueue. This is a bit problematic. ideas?



0
Comment
Question by:cvservices
  • 13
  • 13
26 Comments
 
LVL 22

Expert Comment

by:8080_Diver
Comment Utility
This may not actually solve the problem; however, it can't make it worse and it is where I would start.
How about breaking the AFTER INSERT,DELET trigger into 2 separate triggers, one for AFTER INSERT and one for AFTER DELETE.  My thought is that you may be getting some things a bit cross wired in the trigger.
The other option might be to change the IF . . . ; IF . . . ; construction to one more like
IF . . .
.
.
.
ELSE
IF . . .
.
.
.
So that you only test one or the other of the existences (which is, essentially, achieved by having the separate triggers.
My reason for using the seprarte triggers is that you can get one of them working and that may lead you through getting the other one working.
0
 
LVL 1

Author Comment

by:cvservices
Comment Utility
That's an interesting idea. I'll tinker with that tonight and see if I can get anything to work.
In the big picture though, this seems like a big planning point, because having triggers and relationships all over the place, they'd have to be planned quite meticulously to avoid having these kinds of conflicts.

(now I see why DBAs get paid beaucoup $$ :) )
0
 
LVL 22

Expert Comment

by:8080_Diver
Comment Utility
having triggers and relationships all over the place, they'd have to be planned quite meticulously to avoid having these kinds of conflicts.
Well, yes, they do . . . but, then, isn'tthat how you do the restof the project? ;-)
Seriously, though, slapping triggers on tables in a willy-nilly manner is a recipe for disaster.  Each trigger that fires increases the time required for a transaction to complete.  If you have a transaction that updates a couple of tables and those tables have triggers on them that update 2 more tables each, and those tables have triggers that update 2 more tables each, you care now updating 15 tables each time you update the first one.  Now, if any of these triggers that are getting fired result in accesses to the tables that are being updated or used by the other triggers, then you could easily wind up with deadloscks which will result in a delay (at best) to or a failure of the initial transaction.  
Some of that can be addressed by using covering indexes but the simple fact of 15 tables being updated will probably result in a time out for the intial transaction.  That means that if the transaction got through, say, 13 tables before timing out, it now has to rollback the 13 updates . . . so, it can slow everything down.
And that is to say nothing about the possibility of cascading triggers getting into a circular update condition! ;-)
So, yeah, a bit of planning is required.  That's why a lot of companies either forbid triggers (which is kind of like shooting the horse to keep it from running off, IMHO ;-) or they hire a Database Architect (which is not the same as a Database Administrator) form of a DBA. ;-)
0
 
LVL 1

Author Comment

by:cvservices
Comment Utility
haha, I hear all you're saying. and in response, and not to excuse my bad database design, but, I work in a school district, where I manage desktop OS, Windows Servers, Active Directory, replication, Exchange,  systems integration, and apparently database design.  (of course I get paid, ... well let's not get into that. .:-P)

All this to say, I DEFINITELY will not claim that I am neither a DBA, nor - definitely -  a Database Architect. as a matter of fact, this whole project, started our as a flat database, storing my usernames, only to find out that all my scripting would benefit from a relational database, so I starting learning how to do that, and this DB is my first attempt.

Initially, I was just looking into creating relationships, then I learned that I can put constrains within my tables, and avoid having to do some of that stuff on the app, then learned about triggers, then procedures, naturally, I found the benefit, yet, haven't quite found the balance.

back to our topic, In my question, I'm only dealing with 1 trigger, and I'm not even planning to use more than that. well, I have one more on each table, just updates a timestamp field, but that's all.

I may end up doing away with that trigger however. right now, the trigger works like a charm, but I had to remove the failing cascading relationship as a result.

So, my options/questions to you:
do I:
- spend time to learn how to program around these cascades (keep in mind, I've never even written any T-SQL)
- Remove the cascading relationships, and monitor them from within my app?
- Remove the trigger, and make the changes within my app?

In your experience, what would be the best way to go?

By the way, thanks for the clarification of about the negative side effects of triggers. it totally makes sense.
0
 
LVL 22

Expert Comment

by:8080_Diver
Comment Utility
Let me answer your questions in a sllightly different order.
I would try makeing the changes in the triggers first and only resort to making the changes in the app if that doesn't work.  If the triggers just won't cooperate, then I'd got the route of stored procedures.  This reflects my bias toward laziness, by the away. ;-)  I find it a lot easier to have the data manipulation code in triggers and stored procs than in applications because you can continue to tweatk the SP's and triggers easier than you can alter the app and recompile it. ;-)
Removing the cascading relationships and monitoring them from your app would not be a choice I would favor, either.  Once again, having the database maintain its own integrity is easier and safer than counting on the app being able to do that.
Now, as for "learning how to program around these cascades", well, this is definitely one of the recommendations I'd make.  However, it would follow a recomendation to carefully consider the possibility of a cascade effect whenever you add a trigger and a recommendation that you investigate learning T-SQL.
Some good books (that can sometimes be picked up at quite reasonable prices via the Used selections on Amazon ;-) are:
Transaction-SQL Desk Reference by Lancaster (from Prentice Hall)
SQL COokbook by Molinaro (from O'Reilly)
and, as a general background to relatonal databases and working with them,
The Art of SQL by Faroult (from O'Reilly)
I have a blog (which I need to write a post for, come to think of it ;-) named "SQL Is Your Friend" . . . which is one of my mantras. ;-)  
(Another one of my mantras is, "If we don't have time to do it right the first time . . . when the h*** are we going to do it over?" ;-)
0
 
LVL 1

Author Comment

by:cvservices
Comment Utility
What you're saying makes sense, especially regarding maintaining data integrity within the database.
My dilemma is doing it right vs. the time I have to do it right.
well, the real problem i have is what you had mentioned before. I'm actually stuck with "finding time to do it over" because I initially had a flat DB, and wrote all my apps based on that, and now I'm redesigning because a flat DB is no longer efficient nor useful.

So now I have until August (when school starts) for me to redesign the whole database (correctly) and modify all my scripts to take advantage of my new design. you see my challenge? :)

Thanks for the reference regarding the books, I'm on it, will buy them today.

Now only if I can figure out how to resolve my cascading + trigger problem :)

0
 
LVL 22

Expert Comment

by:8080_Diver
Comment Utility
I would back up and create an INSERT trigger, get it working, and then create a separate DELETE trigger and get it working.
I tend to separate my triggers because, among other things (and this is being lazy again ;-), it is easier to test and debug them that way.  Combined triggers have always seemed to hav way too many interactions that one has to watch out for, IMHO.
I'll see if I can rework your existing SQL into 2 separate triggers.  AS a point of information, it is easier to do that sort of thing if you put the code in the "Code Snippet" portinon of a post. ;-)
0
 
LVL 1

Author Comment

by:cvservices
Comment Utility
Ok, I will try to rework them as separate triggers then and see what I can find out.

The books are on order :)

I will keep in mind your suggestion about the code snipper. I'll insert them there next time instead.
the only reason I hadn't done it, is because you can only insert one block of code, and I can't comment on each of the code snippets individually, so it would make it for a confusing post .. :)  

I'll do it anyway though if it's easier ..
0
 
LVL 22

Accepted Solution

by:
8080_Diver earned 250 total points
Comment Utility
Take a look at the following and see if it looks like it may work.
CREATE TRIGGER [dbo].[Enrollment_InsertTrigger] 

    ON [dbo].[Enrollment] 

AFTER INSERT

 

AS 

BEGIN
 

  SET NOCOUNT ON;
 

	DECLARE @InsertedID	INT;

	DECLARE @CN         VarChar(6); 
 

	SELECT @InsertedID = PermID,

	       @CN	        = CN

	FROM   INSERTED;

 

  DELETE FROM dbo.CourseRemoveQueue 

  WHERE  PermID = @InsertedID

    AND  CN     = @CN;
 

END
 
 

CREATE TRIGGER [dbo].[Enrollment_DeleteTrigger] 

    ON [dbo].[Enrollment] 

AFTER DELETE

 

AS 

BEGIN
 

  SET NOCOUNT ON;
 

	DECLARE @InsertedID	INT;

	DECLARE @CN         VarChar(6); 
 

	SELECT @DeleteID = PermID,

	       @CN	        = CN

	FROM   DELETED;

 

  INSERT INTO dbo.CourseRemoveQueue 

  (

   PermID,

   CN

  )

  VALUES

  (

   @DeletedID,

   @CN

  );
 

END

Open in new window

0
 
LVL 1

Author Comment

by:cvservices
Comment Utility
After a quick look, yes it looks like it will work. i still have to try it. Though, this only separates the two triggers, but won't solve the cascading problem, right?

Or is creating separate triggers is essentially equivalent to write an ELSE statement within the same trigger?
in any case, I'm going to play with it right now, and see what I can come up with.

By the way, I'm very grateful fr your help and guidance! I really appreciate it! :)
0
 
LVL 1

Author Comment

by:cvservices
Comment Utility
By the way, would you care sharing your blog address?
0
 
LVL 22

Expert Comment

by:8080_Diver
Comment Utility
Separating the riggers is sort of like creating a very well controlled ELSE clause in the original one . . .and it makes it easier to debug. ;-)
As for the cascading problem, if you beaar it in mind as you add triggers, you can probably keep yourself out of deep trouble.
0
 
LVL 22

Expert Comment

by:8080_Diver
Comment Utility
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 1

Author Comment

by:cvservices
Comment Utility
Yes, I agree with you.
As you can probably tell, this was my very first trigger, so, you can have the warm and fuzzies to help me with it... LOL  
and I probably fell into the pitfall of its conflicts, as any beginner would. from here on, I'll have to keep it in mind, and probably learn T-SQL more deeply than I ever had planned ... Oh well :)
0
 
LVL 1

Author Comment

by:cvservices
Comment Utility
By the way, just verifying... within this code:

Isn't DECLARE @InsertedID INT; supposed to be DECLARE @DeletedID INT
(and SELECT @DeleteID had a typo on it :)
CREATE TRIGGER [dbo].[Enrollment_DeleteTrigger] 

    ON [dbo].[Enrollment] 

AFTER DELETE

 

AS 

BEGIN

 

  SET NOCOUNT ON;

 

        DECLARE @InsertedID     INT;

        DECLARE @CN         VarChar(6); 

 

        SELECT @DeleteID = PermID,

               @CN              = CN

        FROM   DELETED;

 

  INSERT INTO dbo.CourseRemoveQueue 

  (

   PermID,

   CN

  )

  VALUES

  (

   @DeletedID,

   @CN

  );

 

END

Open in new window

0
 
LVL 1

Author Comment

by:cvservices
Comment Utility
ok . so one more question. Is there some T-SQL syntax that I can add to my trigger to the effect of :
If the delete is coming from within an internal SQL process . i.e: a Cascade, then do not execute this trigger?

Though the syntax that you've given me was much simpler and understandable than the one I had before with statements combined, the conflict between the cascade and the trigger remain there unfortunately.
0
 
LVL 22

Expert Comment

by:8080_Diver
Comment Utility
You are correct in the change to the DELETE trigger.  Sorry about that (of course, I could use the old, "That was a pop quiz." line ;-).
Maybe I am missing something . . . what are you meaning by "the conflict between the cascade and the trigger remain there"?
If the DELETE is happening on Table A, then I thought that the idea was to have an INSERT triggered in Table B.  Similarly, an Insert into Table B should trigger a DELETE in Table A.  If you are worried about DELETING from Table A, triggering an Insert into Table B, and having that trigger a DELETE from Table A, then, in the first place, the second delete won't actually delete anything, so the trigger won't fire that time.
Similarly, a delete from Table B triggering an INSERT into table A triggering a DELETE from Table B stops there because there won't be a row in TABLE B to delete and therefore the trigger won;t fire again.
Yes it will put a slight increase in the transaction time but it shouldn't be a whole lot.
0
 
LVL 22

Expert Comment

by:8080_Diver
Comment Utility
I suppose that you could add a column in each table that would normally be a NULL but would be set to a T if the INSERT was by a trigger.  Then you could retrieve the value for that column as well as the ID and use that to control the DELETE execution.  (It's kind of hard to flag the DELETE as being internal because you don't have a row to put the flag in. ;-)
0
 
LVL 1

Author Comment

by:cvservices
Comment Utility
Thanks for the Pop quiz man, I take it I passed :)

Ok, so ignore the statement about the conflict between the trigger and the cascade, that was malformed :)

Your idea create a flag field sounds doable. However, after thinking some more about it, I think I could do without the trigger, and decided, just from a flow process that it makes sense to have the data in that particular table stay there. I'll tell you what it is, and you can tell me if it makes sense.

I have the table : Course which contains 1 field: CN (course number) which is populated manually by the user within the application. this is used to restrict the lookup in the Student Information System (SIS) (remote database), so that I don't process some 1000 courses, when I'm only interested in 10.

ADCourseMapping: Contains CN (Course Number), SC (School CodE), and ADSG (Active Directory Security Group)

Enrollment: Contains: PermID, and CN

UserStore: which contains a bunch of fields with user information.
CourseRemoveQueue: PermID and CN

Courses is a primary key table, which has cascade update and delete changes to ADCourseMapping  and enrollment. That one works like a charm. Except that, I just noticed that your trigger that you gave me didn't work as expected in this case. When I delete a course number in the Courses table. It deletes the entries in Enrollment, and ADCourseMapping, however, it only inserts the first entry into the CourseRemoveQueue. reverting back to my original trigger worked.

Now there's also a higher up primary table with the PermID of the student. If I delete that student from the database, I want to also remove all enrollment, and course related stuff for that student, hence, my need to cascade from UserStore.PermID to remove the Enrollment entry (which works), and initially, I also wanted to remove the CourseRemoveQueue entry as well (which was the one that's failing, due to the enrollment trigger trying to insert that very record in CourseRemoveQueue. Hope that's making sense.

After thinking about it though, I decided that I want to keep that table available, even if the student gets deleted, as I have separate scripts that are managing the student accounts in Active Directory, and therefore, would prefer to keep this table with its data, so that I can clean up the user's Active Directory Security Group membership.

Phew, that was crazy. If it's hard to follow with the way I worded all this, then please feel free to ignore it all. Bottom line, I think I'm ok with the way I have it now :)


0
 
LVL 22

Expert Comment

by:8080_Diver
Comment Utility
One of the tings that I was about to warn you about is that bulk deletes/inserts can cause issues with triggers.  If you use something like a bulk load or a TRUNCATE the trigges may onely fire once (if at all).  Also, there are cicumstances under which actions can result in the INSERTED containing multiple rows of information (depneding on botht he version of SQL Server and the nature of the SQL being executed.
It deletes the entries in Enrollment, and ADCourseMapping, however, it only inserts the first entry into the CourseRemoveQueue. reverting back to my original trigger worked.
 I missed any references to the second table that was needing to be updated.  
Also, are you saying that the DELETE trigger causes the ADCourseMapping row to delete and you expected the ADCourseMapping information to be in the CourseRemoved table?  I don't see why you would want to have 2 entires that are just alike in the CourseRemoved table or how you would come up with a PermID for a course eing removed based upon the ADCourseMapping table's data.  However, I may be missing something still.
Of course, I am also tying to figure out the purpose of teh CourseRemoved table.  If you unenroll the student from a course, why wouldn't you just re enter the course number in the same manner you originally did in order to re-enroll the student in the course?  Also, if this is just a sort  log of activities, then one normally has an Identity column as teh PK of that table and something like a data to indicate when it was deleted and then one just adds to the table . . . one never deletes from it in order to move things back to the original table (much less having a DELETE trigger on it).
As I said, one has to think through the implications as well as the code for triggers.  Triggers need to be non-circular and to have only limited cascading involved.  For instance, DELETE triggers, in you situation, would naturally flow as follows:
  1. Student (UserStore ?);
  2. Enrollment;
  3. RemovedCourseQueue.
or
  1. Courses;
  2. ADCourseMapping,
  3. Enrollment,
  4. RemovedCourses.
Those can be set up as triggers quite easily.  However, having the RemovedCourses DELETE trigger putting things back into the Enrollment table can run into a conflict due to the course not existing or the Student not existing as well as the looping effect.
By the way, small terminology lesson here, one doesn't usually refer to a table as a "primary key table".  I realize that it is the table whose primary key (PK) matches the Foreign Keys (FKs) in the other tables but the reference is usually made to a 1 to whatever (as in 1 to N or 1:N) relationship between Table A and Table B with cascaded updates (which actually implies that deletes are cascaded as well).  
0
 
LVL 1

Author Comment

by:cvservices
Comment Utility
First off, thanks for the terminology lesson. I'll keep that in mind. There was much more going through my mind as I was trying to explain my situation to you :)

Also, thanks for the warning about the TRIGGERS not always firing. I have read about that in some places, and that is why I'm trying to use them sparingly, until I have a better grasp on TRIGGERS, and their interactions with table relationships/constraints.

As for what's going on. If I read your response correctly, and I think we're just getting entangled in a "lack of details" web, because you're not quite seeing my database, other than in my own words describing it.

The ONLY TRIGGER i have through my whole database (other than the ones on each table updating the date/time stamp) is the one on the enrollment table.

EVERYTHING else is just a relationship with CASCADING UPDATE/DELETE.
The reason I have the CourseRemoveQueue table is because I have some applications that process removed students at a later time, and I use that table for them. Of course, I probably also could've just added another delete type (bit) flag field within the Enrollment table, and that would probably have done the trick I guess :) ... as a matter of fact, perhaps that may be a better way to go, and it's not too late in the game for me to change it. So I guess that would tell you that the CourseRemoveQueue is not a data log table. But thanks for the heads up regarding the identity field on that. I'll make sure to add that whenever I need to create a log table.

As for the circular references you were talking about.
I don't believe I have those.

UserStore Cascades Delete/Update to Enrollment (on the PermID key)
Courses Cascades Delete/Update  to Enrollment and ADCourseMapping
Upon Cascade from either Userstore (in which case the user has been deleted), or from Courses (in which case, the course has been deleted), the student gets un-enrolled from the class, thereby getting added to the CourseRemoveQueue. If that student gets recreated and readded to the course, they will thereby get REMOVED from the CourseRemoveQueue, and get re-added to the Enrollment table, to avoid having an application process the CourseRemoveQueue table, and delete a record that should've actually been active.

I have a few other tables in this whole thing, but they have their own relationships, which don't really affect my process in this case.



t
0
 
LVL 1

Author Closing Comment

by:cvservices
Comment Utility
Have you considered teaching ? :)
0
 
LVL 22

Expert Comment

by:8080_Diver
Comment Utility
After your explanation of the use and purpose of the CourseRemovalQueue, I think I would seriously consider adding an indicator in the Enrollment table; however, I think I would probably add a DateUnenrolled (with a default value of NULL) instead of the Y/N flag.  That provides botha flag and some potentially useful information.
Re: Cascading Updates/Deletes via relationships
Congratulations!  You are at least a step ahead of most initial users of databases in that you have realized that a) you need to maintain the database's data integrity and b) you can set up the relationships and enforce the database integrity by means of those.  (I confess ot having under-estimated you. ;-)
If you can enforce it, you can follow the approach I am taking with a database and application I am working on.  I am forcing the user to take the approach of not using any bulk entry once the database is initially set up.  I have a limited number of key/required tables that have to be populated before anything can be done and those can either be bulk loaded by the user at startup for the whole operation or I may supply a "standard" set of items in them (or a mixture of those two).  However, once the application and database start being used, there will be no provision for bulk loading anything.  That precludes the issues with triggers to a great extent and I am also constraining things so that there will be no updates or deletes that cause multiple records to be involved with a trigger firing.  (And, yes, I is taking some planning. ;-)
So, it sounds like you may pretty much have a handle on this.  By the way, my direct contact (personal email) is Romeo Delta Whiskey 2 at Golf Victor Tango Charlie dot com, if you want to discuss anything else.  (I don't have access to it during the day, though.)
0
 
LVL 22

Expert Comment

by:8080_Diver
Comment Utility
Have you considered teaching ? :)
For a brief period, I taught computer programming ata  Jr. College (mostly because I wanted to find out how people could graduate from one of those with all A's and still not know how to program ;-).
Now I mentor people . . . I've mentored people all around the globe (Scotland, Canada, England, Gremany, Japan, Australia, and Ireland to name a few places ;-).  I was fortunate enough to be mentored by some true heavy-weights in the industry and the only way I can repay them is to mentor others. ;-)
0
 
LVL 1

Author Comment

by:cvservices
Comment Utility
First off, thanks for the compliment of underestimatig me :) that means a lot from a veteran  ;-)

Regarding the date un-enrolled, you do have a good point there, though I'm thinking I may be able to use the DTS field for that purpose in the CourseRemoveQueue, this field is actually populated with a trigger on insert. So that essentially documents the data they un-enrolled, since, this would be the only case this table gets entries inserted. The only other time, is if I'm re-adding that same student to the same course. that's when that entry gets yanked from the CourseRemoveQueue, and gets re-added to the Enrollment table.
I think I got your drift on that one though.

Regarding the bulk adds, you're correct, in fact, the only person that will be doing bulk adds (or application, i must say), is the one that I've written to do that, and it's very likely that only me and another person will ever be using it. The rest is through an individual entry basis, like you mentioned.

You totally cracked me up when you mentioned teaching at a Jr. College mostly to find out how they get A without knowing how to program :) I've actually wondered about that before. my company once hired some lady who had both an MCSE, and a CCNA, and she literally didn't know where the control panel in Windows XP was. (don't ask)

In any case, I would love to be in touch with you, and hope you're up for helping me out with some additional things, because I'm sure they're going to come up, also, you can add me to your list of disciples ... ya know ... for your resume, or personal satisfaction .. whichever is more appropriate in your situation :-P

I'm the type that tries to research before asking, but some of these things are just easier explained than read in the way they work, so I'll keep your email handy ! :)
0
 
LVL 22

Expert Comment

by:8080_Diver
Comment Utility
Consider yourself added to the list . . . however, I must warn you that, based upon my personal history and outlook on life, I give no warranties regarding whether or not you may be led astray . . . I was once told by one of my high school teachers that I could always serve as a good Bad Example? ;-)
As previously mentioned, I do have a personal email account that I underlined, so to speak. ;-)
My thought on the DateUnenrolled on the Enrollment table was that it provides some interesting data mining opportunities as well as controlling whether or not someone is enrolled in  course.  For instance, a given student might show a pattern of enrolling in courses for a period of time and then unenrolling inn all, most, or some of them (e.g. to get some public or business benefit that requires enrollment in a certain number of hours ;-).  Also, since your access to the data for those who are still enrolled could be accomplished by simply requiring the DateUnenrolled to be NULL, re-enrolling a student would only require setting the DateUnenrolled to NULL (which is much less database traffic).  It would also allow you to create reports on how many students had initially enrolled (and at what time) in a semester and then the attrition rate during the semester.  It might even provide some interesting information regarding which courses seemed to have higher ratios of unenrollment to enrollments than others.
By the way, my background is in data analysis, statistics, and that sort of thing. ;-)
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
Read about achieving the basic levels of HRIS security in the workplace.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now