Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Unanswered: stored procedure

    Code:
    CREATE PROCEDURE [delete_EmployeeGamingLicense_1]
    	(@STATUS_1 	[nvarchar],
    	 @TM#_2 	[int],
    	 @LASTNAME_3 	[nvarchar],
    	 @FIRSTNAME_4 	[nvarchar],
    	 @SSN#_5 	[int],
    	 @DEPT#_6 	[nvarchar],
    	 @HIREDATE_7 	[smalldatetime],
    	 @JOBTITLE_8 	[nvarchar])
    
    AS DELETE [GamingCommissiondb].[dbo].[EmployeeGamingLicense] 
    
    WHERE 
    	( [STATUS]	 = @STATUS_1 AND
    	 [TM#]	 = @TM#_2 AND
    	 [LASTNAME]	 = @LASTNAME_3 AND
    	 [FIRSTNAME]	 = @FIRSTNAME_4 AND
    	 [SSN#]	 = @SSN#_5 AND
    	 [DEPT#]	 = @DEPT#_6 AND
    	 [HIREDATE]	 = @HIREDATE_7 AND
    	 [JOBTITLE]	 = @JOBTITLE_8)
    GO
    I would like for these values to be inserted into the another table before they get deleted. How would i go abut altering this procedure to do that, I keep getting error messages. I dont make very many stored procedures still new to it, easier for me to create triggers actually. can some help me out pls
    Last edited by desireemm; 07-23-04 at 14:35.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You could probably do this easier with a trigger, but it isn't tough using a stored procedure either. Just use BEGIN/COMMIT/ROLLBACK TRAN and copy the rows before you delete them. No major trick.

    -PatP

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Don't use triggers when you already have a stored procedure! Unless you want the guy that comes to replace you to either break his back trying to chase your logic, or call you every other day for explanations
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    It's one of those blasted two-edged swords...

    A stored procedure is much more obvious, it makes it pretty clear what is happening and why. A trigger makes it pretty consistant, unless there are logic problems the same action is taken every time, without fail. You can decide on whether you want obvious or consistent behavior.

    That said, I wouldn't recommend mixing a stored procedure solution with a trigger solution. That is a receipe for disaster in my opinion!

    -PatP

  5. #5
    Join Date
    Jun 2003
    Posts
    11
    Hi, If this is your database, and you are just trying to save the data before it is deleted there are 2 options. One is to write the data to a second table for auditing. I generally do not like this option unless you have to store who did the deleting and the date/time of the deletion. If you do not need user info or time date, might I suggest adding a column to your original table Called IsInactive or something like that that you just flag the row as deleted and then your queries just have to ignore the IsDeleted Rows. This allows you to have a purge routine that can be run seperately when you need to delete the extra rows.

    Hope that Helps

    Tal McMahon

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    That's one way. It may lead to drastic table growth if activity on DELETEs is as high or close to INSERTs.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  7. #7
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    It can also lead to some pretty interesting query plans throughout the day on a high transaction table.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    I definitely second that!
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  9. #9
    Join Date
    Jul 2004
    Posts
    1

    Logging into another table

    When u have control over the stored procedure, no need to create trigger for storing data into another table.

    The best solution would be use the Stored procedure itself, and check for the existence of data before deleting.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •