Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2006
    Posts
    3

    Unanswered: Trigger on Update

    Hi,

    I've got 2 tables, EMPLOYEE and STORE

    The EMPLOYEE table holds all the basic stuff you would want to know about an employee and it contains a STORE_CODE attribute that points to the same attribute in the STORE table (to keep track of the store they work at). The STORE table has the STORE_CODE attribute, a NUM_EMP attribute that keeps track of the number of employees at that store, and some other information that is of no relevance to the question.

    I've written the following TRIGGER to update the NUM_EMP attribute in STORE everytime a row is inserted or deleted from EMPLOYEE. It works fine for inserts and deletes but I am clueless as how to make it work for updates (an EMPLOYEE transfers to another store). I appreciate any feedback and please feel free to tell where I've gone wrong so far.

    Thanks!

    Code:
    CREATE TRIGGER [UPDATENUMEMP] ON [EMPLOYEE]
    FOR INSERT,UPDATE,DELETE 
    AS
    
    	DECLARE @STORECODE INT
    	DECLARE @NUMEMP INT
    
    	/*DELETE CASE*/
    	IF (NOT EXISTS (SELECT * FROM INSERTED) AND EXISTS (SELECT * FROM DELETED))
    	BEGIN
    		DECLARE DELETECURSOR CURSOR FOR
    		SELECT	STORE_CODE, COUNT(*) AS NUMEMP
    		FROM		DELETED 
    		GROUP BY 	STORE_CODE
    		
    		OPEN DELETECURSOR
    		
    		FETCH NEXT FROM DELETECURSOR INTO @STORECODE, @NUMEMP
    		WHILE(@@FETCH_STATUS = 0)
    		BEGIN
    			UPDATE	STORE
    			SET		NUM_EMP = NUM_EMP - @NUMEMP
    			WHERE	STORE_CODE = @STORECODE
    			FETCH NEXT FROM DELETECURSOR INTO @STORECODE, @NUMEMP
    		END
    		CLOSE DELETECURSOR
    		DEALLOCATE DELETECURSOR
    	END
    
            /*INSERT CASE*/
    	IF(EXISTS (SELECT * FROM INSERTED) AND NOT EXISTS (SELECT * FROM DELETED))
    	BEGIN
    		DECLARE INSERTCURSOR CURSOR FOR
    		SELECT	STORE_CODE, COUNT(*) AS NUMEMP
    		FROM		INSERTED 
    		GROUP BY 	STORE_CODE
    
    		OPEN INSERTCURSOR
    
    		FETCH NEXT FROM INSERTCURSOR INTO @STORECODE, @NUMEMP
    		WHILE(@@FETCH_STATUS = 0)
    		BEGIN
    			UPDATE	STORE
    			SET		NUM_EMP = NUM_EMP + @NUMEMP
    			WHERE	STORE_CODE = @STORECODE
    			FETCH NEXT FROM INSERTCURSOR INTO @STORECODE, @NUMEMP
    		END
    		CLOSE INSERTCURSOR
    		DEALLOCATE INSERTCURSOR
    	END	
    	GO

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Oh God.

    Cursors.

    In a trigger.

    For both Insert, Update, and Delete.

    To store calculated data.

    ...and all in upper-case.

    You come from planet Oracle, I assume? Welcome to the world of set-based processing. The code you have needs to be scrapped. Please explain what you are trying to do, and we will show you the proper method.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Nov 2006
    Posts
    3
    Yeah, actually I'm a student right now and the textbook we are using uses mostly Oracle examples....

    I don't really know what more to say about what I'm trying to do that I didn't say in the original post but I'll try. I have 2 tables, one of which keeps track of employees and one that is a list of store locations.

    Code:
    EMPLOYEE TABLE
    EMP_CODE  EMP_TITLE  EMP_LNAME  EMP_FNAME  EMP_INITIAL  STORE_CODE
    1	    Mr.	    Williamson	 John	           W	       3
    2	    Ms.	    Ratula	  Nancy	 	                2
    3	    Ms.	   Greenboro	Lottie	           R	       4
    4	    Mrs.    Rumpersfro	Jennie	          S	      5
    5	    Mr.	    Smith	   Robert	     L		 3
    6	    Mr.	   Renselaer	Cary	            A		1
    7	    Mr.	    Ogallo	    Roberto	     S		3
    8	    Ms.	   Johnsson	Elizabeth	   I	       1
    9	    Mr.	    Eindsmar	Jack	            W		2
    10	   Mrs.	   Jones	    Rose	     R		 4
    Code:
    STORE TABLE
    STORE_CODE        STORE_NAME    REGION_CODE     EMP_CODE    NUM_EMP
    1	Access Junction		2	             8           2
    2	Database Corner	         2	            12	        2
    3	Tuple Charge		 1	              7	       3
    4	Attribute Alley	         2	              3	        2
    5	Primary Key Point	1	             15	        1
    The NUM_EMP column keeps a count of how many employees are employed at that store. I need a trigger that will change the NUM_EMP value if an employee record is added or deleted or if the STORE_CODE value of an existing employee record is changed. I know my trigger isn't pretty much it works for insertions or deletions. I just don't know how to make it work for updates.

    Thanks!

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I'm not in blindman's league, but why would you go to so much trouble to store something that can be so easily calculated from the employee table?
    Paul

  5. #5
    Join Date
    Nov 2006
    Posts
    3
    I agree it's a pretty complicated and stupid way of getting the information, but like I said before, I'm a student, and I think the whole purpose of this headache is just to know that I can do it.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    So this is a homework assignment?

    Before we help you any more with this, you need to read the sections in Books Online about SELECT statements and TRIGGERS. Then take a stab at writing this code without a cursor. Hint: write an UPDATE statement that will perform you action, and then turn it into a trigger.
    Also, you should separate the insert/update from the delete as separate triggers.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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