March 11, 2004 at 6:36 am
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>
March 11, 2004 at 6:59 am
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.
March 11, 2004 at 9:43 am
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
March 11, 2004 at 10:15 am
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
March 12, 2004 at 5:32 am
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