Results 1 to 15 of 15
  1. #1
    Join Date
    Dec 2002
    Posts
    13

    Question Unanswered: Beginner need help ... with triger?

    I have a table that records data and I want to be able to copy a few of the feilds to another table in a second database whenever the data is inserted or updated. I assume the way to do that is thru the use of a trigger, but have no clue how to begin.

    For example:

    db #1 Table User has fields;
    id, name, address, zip, password, date

    db #2 table n_user has fields;
    id, name, password

    Hope thats enough information to get some assistance...
    Unholy

    Online Gamers Guild
    www.ogguild.com

  2. #2
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249

    Re: Beginner need help ... with triger?

    use [Your active DB]
    GO
    create trigger [TR_User(,I,U,)]
    on dbo.[user]
    for insert,update
    as begin
    insert [Your history DB].dbo.[n_user] ([id], [name], [password])
    select [id], [name], [password]
    from inserted
    end

  3. #3
    Join Date
    Dec 2002
    Posts
    13
    Thanks for you help.
    I made the trigger but get this error on update of one field value.

    Cannot insert explicit value for identity column in table 'FORUM_MEMBERS' when IDENTITY_INSERT is set to OFF.

    Any clue????

  4. #4
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    Difference between history and active table is difference between picture and movie.
    You cannot use the same PK in both tables.

    In [Your history DB].dbo.[n_user] table:
    1,Add [oldid] column with datatype of [user].[id] column, PK will be on [n_user].[id] column.
    2,Also consider using larger datatype on [n_user].[id] column, if [user] table is modified frequently.

    3,Modify trigger:

    use [Your active DB]
    GO
    alter trigger [TR_User(,I,U,)]
    on dbo.[user]
    for insert,update
    as begin
    insert [Your history DB].dbo.[n_user] ([oldid], [name], [password])
    select [id], [name], [password]
    from inserted
    end
    GO
    delete [Your history DB].dbo.[n_user]
    declare @x int
    update dbo.[user] set @x=1

    4...This will help sometimes
    Adding FingerPrint timestamp column NOT NULL
    Adding CreatedDate datetime column NOT NULL with default getdate().

    5,Securing history table:

    use [Your history DB]
    GO
    Deny insert,update,delete,references on dbo.[n_user] to public

  5. #5
    Join Date
    Dec 2002
    Posts
    13

    Thumbs up

    I see parts in the code for insert and delete.... what about update?

    Again... Thank you very much for your help.

  6. #6
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    /*
    You wrote:
    "I have a table that records data and I want to be able to copy a few of the feilds to another table in a second database
    WHENEVER THE DATA IS INSERTED OR UPDATED."
    So you did not specify DELETED info.
    */

    /* COMMENTED CODE */
    --Switching to your active DB
    use [Your active DB]
    GO
    --Creates trigger FOR INSERT,UPDATE on dbo.[user]
    --This trigger uses INSERTED table of NEW VALUES for BOTH INSERT AND UPDATE
    --( Look at "inserted tables" topic in BOL )
    if object_id('TR_User(,I,U,)') is not null drop trigger [TR_User(,I,U,)]
    GO
    create trigger [TR_User(,I,U,)]
    on dbo.[user]
    for insert,update
    as begin
    insert [Your history DB].dbo.[n_user] ([oldid], [name], [password])
    select [id], [name], [password]
    from inserted
    end
    GO
    --Deleting test rows in HISTORY
    delete [Your history DB].dbo.[n_user]
    --Filling history table with previosly inserted data (prehistoric)
    declare @x int
    update dbo.[user] set @x=1

  7. #7
    Join Date
    Dec 2002
    Posts
    13
    Ok... looks like the insert new member fires off the trigger just fine. Both tables are updated correctly.

    However, when a user tries to update their profile... They recieve an sql error stating a primary key violation.

    Here is the exact trigger that causes that error.

    ==============

    CREATE trigger [TR_Players(,I,U,)]
    on dbo.[players]
    for insert,update
    as begin
    insert [Forum].dbo.[FORUM_MEMBERS] (M_name, M_username, M_password, M_email, M_quote)
    select [PEmail], [PEmail], [Ppassword], [PEmail], [pcomments]
    from inserted
    end

    =============

    Now I went ahead and edited the trigger to be this,

    =============

    CREATE trigger [TR_Players(,I,)]
    on dbo.[players]
    for Insert
    as begin
    Insert [Forum].dbo.[FORUM_MEMBERS] (M_name, M_username, M_password, M_email, M_quote)
    select [PEmail], [PEmail], [Ppassword], [PEmail], [pcomments]
    from inserted
    end

    =============

    As you might expect... this fires off correctly and both tables get their new information.

    Now the question is how do I get the update trigger to update table 2 (FORUM_MEMBERS) without attempting to add another row with the same information, thus violating the pk constraint.

    I tried to add a second trigger to the same table just for updates like such.

    =============

    CREATE trigger [TR_Players(,U,)]
    on dbo.[players]
    for Update
    as begin
    Insert [Forum].dbo.[FORUM_MEMBERS] (M_name, M_username, M_password, M_email, M_quote)
    select [PEmail], [PEmail], [Ppassword], [PEmail], [pcomments]
    from inserted
    end

    =============

    Too bad that gave me the exact same error. Also, syntax wise... im kind of confused why I had to use "inserted" and not "updated" to get successful syntax checking??? Does the temp table updated not exist with triggers? Can I not run two seperate triggers against the same table?

    Oh and as an FYI. I did not want delete syntax... I was just commenting that in your post i saw the keyword delete?



    Like always.... thanks very much for your time to deal with my issues..

  8. #8
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    CREATE trigger [TR_Players(,U,)]
    on dbo.[players]
    for Update
    as begin
    update fm set
    fm.M_name = i.[PEmail]
    , fm.M_username = i.[PEmail]
    , fm.M_password = i.[Ppassword]
    , fm.M_email = i.[PEmail]
    , fm.M_quote = i.[pcomments]
    from [Forum].dbo.[FORUM_MEMBERS] fm
    join inserted i
    on fm.M_name=i.[PEmail] --PK join - verify
    end

    --for more See http://dbforums.com/showthread.php?threadid=640545

  9. #9
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    CREATE trigger [TR_Players(,U,)]
    on dbo.[players]
    for Update
    as begin
    update fm set
    fm.M_name = i.[PEmail]
    , fm.M_username = i.[PEmail]
    , fm.M_password = i.[Ppassword]
    , fm.M_email = i.[PEmail]
    , fm.M_quote = i.[pcomments]
    from [Forum].dbo.[FORUM_MEMBERS] fm
    join inserted i
    on fm.M_name=i.[PEmail] --PK join - verify
    end

    --for more See http://dbforums.com/showthread.php?threadid=640545

  10. #10
    Join Date
    Dec 2002
    Posts
    13

    Thumbs up

    You are like a God to me! Works great. Thank You Thank you!

  11. #11
    Join Date
    Dec 2002
    Posts
    13

    Red face Here I go again

    It seems that I need to make another trigger to pass changed passwords back to the original table. I attempted to modify the previous trigger to work in reverse. But no suck luck. I could use alittle assistance here.

    I want the trigger to take the M_password field from the forum_members table (when its updated) and update the other database
    table players with the new password. Here is what I came up with...

    ------------------------------------------------
    CREATE trigger [TR_pw(,U,)]
    on [Forum].dbo.[FORUM_MEMBERS]
    for Update
    as begin
    update pl set
    pl.Ppassword = i.[M_password]
    from dbo.[players] pl
    join inserted i
    on pl.pemail=i.m_name --PK Join - verify
    end
    ------------------------------------------------

    Thanks.

  12. #12
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    Your triggers are probably chaining, learn more about nesting
    http://dbforums.com/showthread.php?threadid=640545

  13. #13
    Join Date
    Dec 2002
    Posts
    13
    you know your prolly correct... cuz the error message im recieving says something about exceeding the number of database connections.

    Ive read the post you refered me to... but am still not sure what it said. Sorry... im new to this. Can I assume that my trigger is formed correctly? But I need to some how limit its ability to fire from an update by another trigger?

  14. #14
    Join Date
    Dec 2002
    Posts
    13
    Stike that...
    I went to BOL and found how to turn off recursive triggers... I turned them off and it looks like its working. Is there any effect on regular system operation due to this trigger setting?

    Thanks for your help.

  15. #15
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    You can put "if trigger_nestlevel(@@procid)>1 return" in the beginning of your data modifying trigger. Checking-only triggers stay active in the trigger chain. Also even with switching nested triggers off I cannot make Ver1 (table mirroring) working. Also when you are using recursive algorithm for trigger, you must have recursive triggers on.
    In most cases you do not need it.

    --Ver1
    create table A(X int)
    create table B(X int)
    GO
    create trigger tiA on A for insert as
    insert B select * from inserted
    GO
    create trigger tiB on B for insert as
    insert A select * from inserted
    GO
    insert A values (1)
    GO
    drop table A
    drop table B

    --Ver2
    create table A(X int)
    create table B(X int)
    GO
    create trigger tiA on A for insert as
    if trigger_nestlevel(@@procid)>1 return
    insert B select * from inserted
    GO
    create trigger tiB on B for insert as
    if trigger_nestlevel(@@procid)>1 return
    insert A select * from inserted
    GO
    insert A values (1)
    GO
    drop table A
    drop table B

    Good luck !

Posting Permissions

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