Help: Triggers to insert records

  • Dear everyone,

    I have an error on trigger.

    The trigger is aim at inserting record in BOOK database when new records inserted by user in SYSTEMDATA database.

    For example, if 2 records inserted in SYSTEMDATA, 2 records should also be inserted in BOOK.

    But the error is that there ar 3 records in BOOK when 2 ar inserted in SYSTEMDATA.

    I don't know what error I have made in my trigger. Could anyone tell me what happen?? And How can I fix it??

    <code>

    create  trigger AfterInsertSysData

    on systemDataTest

    after insert

    as

    insert into fac_test (rid, fname, book_date)

    select r.rid, fname, book_date from systemDataTest s, res_test r

    where substring(upper(r.type),1,1) in ('F','M','U','R') and

    r.rid = (select rid from inserted)

    </code>

     

  • First look indicates that you have not specified how to join systemDataTest and res_test which will result in a cartesian join. Can you post table ddl.

    Far away is close at hand in the images of elsewhere.
    Anon.

  • Thanks you for reply!!!

    systemDataTest DDL:

    sysID int primary key,

    rid char(4),

    fname char(8),

    fplace char(10),

    book_date datetime

     

    res_test DDL:

    rid char(4) primary key,

    type char(4),

    phone char(6)

     

    Thanks you for help!!!!!!

     

     

    res_test

  • Try this:

    CREATE TRIGGER dbo.AfterInsertSysData ON SystemDataTest FOR INSERT

    AS

    IF EXISTS(SELECT rid FROM Inserted)

    BEGIN

    INSERT INTO Books(rid,fname,book_date)

    SELECT s.rid,s.fname,s.book_date)

    FROM SystemData s, inserted i

    WHERE s.rid=i.rid

    END

    ELSE

    BEGIN

    RAISERROR('No rows were inserted into the SystemData table.',16,1)

    END

     

     

     

  • CREATE TRIGGER AfterInsertSysData

    ON SystemDataTest

    AFTER INSERT

    AS

    INSERT INTO Books (rid,fname,book_date)

    SELECT s.rid,s.fname,s.book_date

    FROM inserted i

    INNER JOIN SystemData s

    ON s.rid = i.rid

    INNER JOIN res_test r

    ON r.rid = s.rid

    AND UPPER(SUBSTRING(r.type,1,1)) IN ('F','M','U','R')

    Far away is close at hand in the images of elsewhere.
    Anon.

Viewing 5 posts - 1 through 4 (of 4 total)

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