Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2004
    Posts
    6

    Unanswered: triggers question

    I have a Table 'emp' in database 'database1'

    empid empname
    1 john
    2 kerry
    3 paul

    And i wanted to create a trigger so that it should fire and effect the table 'emp' in the database 'database2', whenver there is an insert,update or delete on that table.I want to this to be done in a single trigger.

    Can anyone give me the sample code for this.

    Thanks in advance.

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    For the trigger to properly function you'll have to make sure that every user in database1 is also present in database2.

    For sample codes look into Books Online. In QA type "create trigger" without quotes, highlight both words, and press Shift-F1.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    create table emp(empid int, empname varchar(10))
    go
    create trigger tr_emp on emp
    for insert, update, delete
    as
    if not exists(select 1 from deleted) begin
    insert test33.dbo.emp
    select * from inserted
    return
    end

    if not exists(select 1 from inserted) begin
    delete test33.dbo.emp
    where empid in(select empid from deleted)
    return
    end

    update test33.dbo.emp
    set empname=i.empname
    from inserted i where i.empid=test33.dbo.emp.empid
    go

  4. #4
    Join Date
    Apr 2004
    Posts
    6
    I am getting this error when i'm trying to insert a row into server2.tab1 when there is an insert in the table server1.tab1

    I tried this with procedure call thru triggers.


    Server: Msg 7395, Level 16, State 2, Procedure Insert_into_job, Line 6
    Unable to start a nested transaction for OLE DB provider 'SQLOLEDB'. A nested transaction was required because the XACT_ABORT option was set to OFF.
    [OLE/DB provider returned message: Cannot start more transactions on this session.]
    OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionLocal:tartTransaction returned 0x8004d013: ISOLEVEL=4096].

    So where should i change the property of xact_abort to ON.


    Thanks in advance.

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    You didn't say the db's were on 2 different servers. I, as well as everybody else, understood that it's the same server. Look up Linked Servers in BOL, you need to start looking into it before posting here (though have to admit, - the more I have to do it for other people, the more I learn myself about things that I thought I already knew )
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  6. #6
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Originally posted by rdjabarov
    You didn't say the db's were on 2 different servers. I, as well as everybody else, understood that it's the same server. Look up Linked Servers in BOL, you need to start looking into it before posting here (though have to admit, - the more I have to do it for other people, the more I learn myself about things that I thought I already knew )
    Exactly!

  7. #7
    Join Date
    Apr 2004
    Posts
    6
    I have table emp and I might update any of the row and it should be affected in the other table(emp in a different db) with a trigger.
    How can i write that update trigger.
    How do i know which values to update

    emp id emp_name
    1 jj
    2 kkkk
    3 lllll
    4 mmmm

    Now i want to change 2 record from -2 kkkkkk to 2 sssss
    How can i do that with trigger and update in the other db?
    thanks.

Posting Permissions

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