Post reply

Need help with an Update Trigger

  • June 7, 2005 at 10:28 am

    #164522

    This is probably really simple but I am really knew at this type of sql work.  I created a custom table and two triggers that will populate that table.  I've gotten both of them to fire and fill in data however the 2nd of the two (The Update Trigger) is updating all the records in the table instead of just the one it is suppose to.  If i need to add some more background let me know.  Thank you in advance for any help.

    Ryan

    --Creates Trigger called LogOn_Insert

    --on Access Table when record is inserted

    --to populate the custom table "LOGON" so that frequency and

    --duration of users time in the DB can be tracked

     
    GO

    If EXISTS (Select name from sysobjects where name = 'LogOn_Insert'

    and type = 'TR')

     
    DROP TRIGGER LogOn_Insert
     
    GO
     
    CREATE TRIGGER LogOn_Insert

    on ACCESS

    For

    INSERT

    as INSERT LogOn

    (AccessDate, AccessNbr, CompanyID, DatabaseName, ScrnNbr,

    SessionCntr, UserId)

     
    SELECT GETDATE(), AccessNbr, CompanyID, DatabaseName, ScrnNbr,

    SessionCntr, UserId from inserted WHERE SCRNNBR = 'MENU '

     
     
    --Creates Trigger called LogOn_Update

    --on Access Table when record is deleted

    --updates "LogOffDate" in LogOn Table
     
    GO

    If EXISTS (Select name from sysobjects where name = 'LogOn_Update'

    and type = 'TR')

     
    DROP TRIGGER LogOn_Update

    GO

     
    CREATE TRIGGER LogOn_Update

    on ACCESS

    For

    DELETE

    as UPDATE  LogOn  SET LogoffDate = GETDATE()

     
    select * from deleted, logon where logon.scrnnbr = deleted.scrnnbr

    and logon.userid = deleted.userid and logon.accessnbr = deleted.accessnbr

     
     

     

    Ryan Grant
    Financial Systems Consultant
    Elypsis, Inc
    ryan@elypsis.com

  • June 7, 2005 at 2:26 pm

    #563661

    Your trigger updates all rows becuase your update statement has NO where clause.  The Select is just hanging out there attached to nothing.  You might be looking for something like this.

    ** This is wrong - see Remi's post below **

    CREATE TRIGGER LogOn_Update

    on ACCESS

    For

    DELETE

    as

    UPDATE  LogOn 
    SET LogoffDate = GETDATE()
    Where 
           logon.scrnnbr = deleted.scrnnbr and 

           logon.userid = deleted.userid and

           logon.accessnbr = deleted.accessnbr

    Consider renaming this to LogOn_Delete as it is designed to fire when rows are deleted from Access.

  • June 7, 2005 at 2:30 pm

    #563664

    To finish Ron's thaught :

    CREATE TRIGGER LogOn_Delete

    on ACCESS

    For

    DELETE

    as

    SET NOCOUNT ON

    UPDATE L

    SET LogoffDate = GETDATE()

    FROM LogOn L inner join Deleted D on L.scrnnbr = D.scrnnbr and L.userid = D.userid and L.accessnbr = D.accessnbr

  • June 7, 2005 at 2:40 pm

    #563671

    Show off

  • June 7, 2005 at 2:45 pm

    #563675

    I'm trying to stop... but I like the users to have the right answer.. sorry about that .

  • June 7, 2005 at 2:58 pm

    #563683

    Was there something wrong with my post??

    I'm always open to suggestions for improvement.

  • June 7, 2005 at 3:02 pm

    #563687

    hmm.. I don't see any reference to the deleted table in here :

    UPDATE LogOn

    SET LogoffDate = GETDATE()

    Where

    logon.scrnnbr = deleted.scrnnbr and

  • June 7, 2005 at 3:07 pm

    #563691

    So I'm still a show off??

  • June 7, 2005 at 3:07 pm

    #563690

    duh! Thanks

  • June 7, 2005 at 3:19 pm

    #563699

    Well...just a little...but that's ok.

    I'd just looked at my triggers and at least I didn't make that mistake there!

  • June 7, 2005 at 5:04 pm

    #563711

    I don't think they would compile if the table name is correct. But I can't test from here, maybe tomorrow.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply