Results 1 to 11 of 11
  1. #1
    Join Date
    Nov 2003
    Posts
    24

    Unanswered: Trigger and any number of updates

    I've a system of users and let's call em subusers. Every User becomes an automatic generated login when entered into the database. Every subuser has a reference to his user and no login, cause only thr root in the chain is able to login. But when the user gets deleted, all subusers become a new user. I've done this with a trigger changing the superUser Value=0:
    Code:
    create trigger abc on Users AFTER DELETE
    as
      declare @h int
      SELECT @h = id FOM inserted
      UPDATE Users SET superUser=0 WHERE superUser=@h
    Furthermore the trigger deletes all additionally data of the user.
    Since every subuser of the deleted user becomes a user himself for every subuser a new Login must be created. I'm using an update triger for this task:
    Code:
    1: create trigger userUpdate on Users After Update
    2: AS
    3: DECLARE @superold int
    4:   @supernew int
    5:   @name nvarchar(55)
    6:   @date smalldatetime
    7: if UPDATE(superUser)
    8: begin
    9:   SELECT @superold= superUser FROM deleted
    10:   SELECT @supernew=superUser FROM inserted
    11: 
    12:   if @superold <> @supernew
    13:   begin
    14:     if @superold = 0
    15:     begin
    16:       DELETE FROM UserLogin WHERE id=@superold
    17:     end
    18:     else if @supernew=0
    19:     begin
    20:       SELECT @name=Name,@date=Date from inserted
    21:       execute createLogin @supernew,@name,@date
    21:     end
    22:   end
    23: end
    The problem is in line 20 and 21, cause the values @name and @date containing only the last updated user(the last entry in the inserted table) thus only for the last user a new Login is created whereas the others become the state user but no login was created. What i need is a method to loop over all entrys in the tables inserted btw. deleted.
    Does anybody know how to achieve this, looping the tables and executing a stored procedure for every entry?

    bye

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    It's not looping....(although I think you could use a cursor)

    You have to think in set based terms...

    an inserted or dseleted table can an 1 to many rows....

    Your delete would be

    DELETE FROM UserLogin WHERE id IN SELECT id FROM deleted)

    As far as the sproc goes, you'd probably need a cursor....

    I'm just not to keen of your entire concept though....

    In my own opinion (MOO) this doesn't belong in a cursor...

    Plus I'm not to keen on the whole login automation thing...

    doesn't seem to secure...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    I agree with Brett. Of course you can avoid the cursor but it might be actually slower (select @name=min(Name) from inserted where Name > @name.)

  4. #4
    Join Date
    Sep 2003
    Posts
    65
    lol, yea.

    here is the code for a cursor:

    (part generic code, but just a paste (with missing pieces) from a project i was working on)
    Code:
    ON Member
    AFTER UPDATE
    AS
    BEGIN
    
    	DECLARE @MemberID int;
    
    	DECLARE c3i CURSOR 
    	FOR SELECT inserted.MemberID FROM inserted;
    		OPEN c3i;
    		FETCH NEXT FROM c3i INTO @MemberID;
    		WHILE @@FETCH_STATUS = 0
    		BEGIN
    			--Do stuff
    
    			FETCH NEXT FROM c3i INTO @MemberID;
    		END
    	CLOSE c3i;
    	DEALLOCATE c3i;
    
    END
    -Ashleigh

  5. #5
    Join Date
    Sep 2003
    Posts
    522
    isn't it from bol?

  6. #6
    Join Date
    Sep 2003
    Posts
    65
    There would be somthing similar to that in bol [books online] as well. I tend not to use bol as much as I should though, I'm a big fan of paper back books, lol. something I can sit on my lap and thumb through.
    -Ashleigh

  7. #7
    Join Date
    Sep 2003
    Posts
    522
    i like that bit too :-)

  8. #8
    Join Date
    Nov 2003
    Posts
    24
    ok, it will work with cursors, but do i realy need to have a var for every column in the table?

  9. #9
    Join Date
    Sep 2003
    Posts
    65
    No, you just need the PK, or a unique identifier, then you can get the rest from that.

    An example:
    When I update a member, I want to set update time to the current time:

    Code:
    ON Member
    AFTER UPDATE
    AS
    BEGIN
    
    	DECLARE @MemberID int;
    
    	DECLARE c3i CURSOR 
    	FOR SELECT inserted.MemberID FROM inserted;
    		OPEN c3i;
    		FETCH NEXT FROM c3i INTO @MemberID;
    		WHILE @@FETCH_STATUS = 0
    		BEGIN
    			Update Member
    			Set LastUpdate = GetDate()
    			Where MemberID = @MemberID
    
    			FETCH NEXT FROM c3i INTO @MemberID;
    		END
    	CLOSE c3i;
    	DEALLOCATE c3i;
    
    END
    Thanks,
    -Ashleigh

  10. #10
    Join Date
    Nov 2003
    Posts
    24
    ok thanks, I've overseen that i can choose the vars i need in the FOR SELECT ... clause .
    IT's just to early in the morning

  11. #11
    Join Date
    Sep 2003
    Posts
    65
    lol, no worried mate, glad I could help.

    when your using more than one variable, the order is what determines which variable holds what information.

    ;-)
    -Ashleigh
    -Ashleigh

Posting Permissions

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