Results 1 to 3 of 3

Thread: trigger problem

  1. #1
    Join Date
    Apr 2004
    Posts
    6

    Unanswered: trigger problem

    CREATE TRIGGER insert_into_emp ON emp
    FOR INSERT,UPDATE,DELETE
    AS

    SET XACT_ABORT ON

    if not exists(select emp_id,dept_id from deleted)
    begin

    INSERT INTO aaa.bbb.dbo.emp(emp_id,emp_name,emp_address,dept_i d)

    SELECT emp_number as emp_id,emp_full_name as emp_name,emp_address,dept_id

    FROM inserted
    return
    end

    if not exists(select emp_id,dept_id from inserted)
    begin
    delete FROM aaa.bbb.dbo.emp a INNER JOIN deleted d
    ON a.emp_id=d.emp_number and a.dept_id=d.dept_id

    return
    end

    UPDATE a SET a.emp_name=i.emp_full_name
    a.emp_Address=i.emp_address
    FROM aaa.bbb.dbo.emp a INNER JOIN inserted i
    ON a.job_number=i.job_id AND a.event_id=i.event_id

    In my table combination of emp_id and dept_id makes a row unique.Its a compsite key.

    emp_id dept_id emp_name emp_Address
    1 1 hh jj
    1 2 uu ii
    2 2 jj kk
    3 1 ll ee
    4 1 mm oo
    3 2 ss tt


    I wanted to update the record for emp_id ='3' and dept_id='1'.My trigger is not working for that and giving an error saying that' The database row you are modifying no longer exists in the database'
    Can anyone correct my code.

  2. #2
    Join Date
    Apr 2004
    Posts
    6
    Is't possible to implement this through triggers?if i update any of the above record it should be affected in other table.I couldn't do that with the above logic.can anyone help me in this.
    Thanks.

  3. #3
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Originally posted by ambrose_sql
    Is't possible to implement this through triggers?if i update any of the above record it should be affected in other table.I couldn't do that with the above logic.can anyone help me in this.
    Thanks.
    This works fine for me. Only you cannot update id - remove row and add another with new id.

    create table ltable(id int,code varchar(10))
    go
    alter TRIGGER tr_ltable ON ltable
    FOR INSERT,UPDATE,DELETE
    AS

    SET XACT_ABORT ON

    if not exists(select * from deleted)
    begin
    INSERT myserver.testdb.dbo.ltable
    SELECT * FROM inserted
    return
    end

    if not exists(select * from inserted)
    begin
    delete a
    FROM myserver.testdb.dbo.ltable a JOIN deleted d ON a.id=d.id
    return
    end

    UPDATE a SET a.code=i.code
    FROM myserver.testdb.dbo.ltable a INNER JOIN inserted i
    ON a.id=i.id

    return
    go
    select * from ltable
    select * from myserver.testdb.dbo.ltable

    insert ltable values(1,'1')
    insert ltable values(3,'3')
    delete ltable where id=1
    update ltable set code='33' where id=3

Posting Permissions

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