February 14, 2008 at 1:21 pm
i got this message when i was executing my trigger.. the error message is Msg 1013, Level 16, State 1, Procedure tr_v_OrganizationDetailType, Line 8
The objects "OrganizationDetail" and "OrganizationDetail" in the FROM clause have the same exposed names. Use correlation names to distinguish them. What shud i do to solve that problem...
below is my trigger
create view v_OrganizationDetailType as
select Type_name, Org_name, Org_address, Org_tel_no, Org_fax_no, Org_email, Org_description
from OrganizationType inner join OrganizationDetail on OrganizationType.Type_application_id =OrganizationDetail.Type_application_id
go
create trigger tr_v_OrganizationDetailType on v_OrganizationDetailType instead of insert as
BEGIN
insert OrganizationType (Type_name)
select distinct inserted.Type_name
from inserted left join OrganizationType on inserted.Type_name = OrganizationType.Type_name
where OrganizationType.Type_name IS NULL /*** Exclude Organization Types already in the table ***/
insert OrganizationDetail (Org_name, Org_address, Org_tel_no, Org_fax_no, Org_email, Org_description, Type_application_id)
select distinct inserted.Org_name, inserted.Org_address, inserted.Org_tel_no,
inserted.Org_fax_no, inserted.Org_email, inserted.Org_description, OrganizationType.Type_application_id
from inserted inner join OrganizationType on inserted.Type_name = OrganizationType.Type_name
left join OrganizationDetail on inserted.Org_name = OrganizationDetail.Org_name
left join OrganizationDetail on inserted.Org_address = OrganizationDetail.Org_address
left join OrganizationDetail on inserted.Org_tel_no = OrganizationDetail.Org_tel_no
left join OrganizationDetail on inserted.Org_fax_no = OrganizationDetail.Org_fax_no
left join OrganizationDetail on inserted.Org_email = OrganizationDetail.Org_email
left join OrganizationDetail on inserted.Org_description = OrganizationDetail.Org_description
where OrganizationDetail.Org_name IS NULL /*** Exclude Organization Detail already in the table ***/
END -- trigger def
go
February 14, 2008 at 1:38 pm
insert OrganizationDetail (Org_name, Org_address, Org_tel_no, Org_fax_no, Org_email, Org_description, Type_application_id)
select distinct inserted.Org_name, inserted.Org_address, inserted.Org_tel_no,
inserted.Org_fax_no, inserted.Org_email, inserted.Org_description, OrganizationType.Type_application_id
from inserted inner join OrganizationType on inserted.Type_name = OrganizationType.Type_name
left join OrganizationDetail on inserted.Org_name = OrganizationDetail.Org_name
left join OrganizationDetail on inserted.Org_address = OrganizationDetail.Org_address
left join OrganizationDetail on inserted.Org_tel_no = OrganizationDetail.Org_tel_no
left join OrganizationDetail on inserted.Org_fax_no = OrganizationDetail.Org_fax_no
left join OrganizationDetail on inserted.Org_email = OrganizationDetail.Org_email
left join OrganizationDetail on inserted.Org_description = OrganizationDetail.Org_description
where OrganizationDetail.Org_name IS NULL /*** Exclude Organization Detail already in the table ***/
shouldn't that be
insert OrganizationDetail (Org_name, Org_address, Org_tel_no, Org_fax_no, Org_email, Org_description, Type_application_id)
select distinct inserted.Org_name, inserted.Org_address, inserted.Org_tel_no,
inserted.Org_fax_no, inserted.Org_email, inserted.Org_description, OrganizationType.Type_application_id
from inserted inner join OrganizationType on inserted.Type_name = OrganizationType.Type_name
left join OrganizationDetail on inserted.Org_name = OrganizationDetail.Org_name
and inserted.Org_address = OrganizationDetail.Org_address
and inserted.Org_tel_no = OrganizationDetail.Org_tel_no
and inserted.Org_fax_no = OrganizationDetail.Org_fax_no
and inserted.Org_email = OrganizationDetail.Org_email
and inserted.Org_description = OrganizationDetail.Org_description
where OrganizationDetail.Org_name IS NULL
/*** Exclude Organization Detail already in the table ***/
February 14, 2008 at 9:26 pm
i've changed the trigger command that u given to me buat when i was insert the value, i got this message Msg 512, Level 16, State 1, Procedure trg_InsertOrganization, Line 11
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, = or when the subquery is used as an expression.
The statement has been terminated.
here is my command insert...
insert v_OrganizationDetailType
select 'PTM', 'Sequ Inspection', 'Cheras', '0343234321', '0343234322', 'Sequ@inspec.com', 'Oil and Gas' UNION
select 'PTM', 'Alpha', 'Bangi', '0344567654', '0344567653', 'Alpha@yahoo.com', 'Hospitality Consultant'
February 14, 2008 at 10:07 pm
Your left join is messing you up. Using left joins with additional join filters is not the most reliable method to return data. You should use the where clause to filter the data.
Your trigger should look like this:
insert OrganizationDetail (Org_name, Org_address, Org_tel_no, Org_fax_no, Org_email, Org_description, Type_application_id)
select distinct inserted.Org_name,
inserted.Org_address,
inserted.Org_tel_no,
inserted.Org_fax_no,
inserted.Org_email,
inserted.Org_description,
OrganizationType.Type_application_id
from inserted inner join OrganizationType on inserted.Type_name = OrganizationType.Type_name
left join OrganizationDetail on inserted.Org_name = OrganizationDetail.Org_name
WHERE
inserted.Org_address = OrganizationDetail.Org_address
and inserted.Org_tel_no = OrganizationDetail.Org_tel_no
and inserted.Org_fax_no = OrganizationDetail.Org_fax_no
and inserted.Org_email = OrganizationDetail.Org_email
and inserted.Org_description = OrganizationDetail.Org_description
AND OrganizationDetail.Org_name IS NULL
February 14, 2008 at 10:21 pm
i have changed using ur recommendation... but when im trying to insert values into table v_OrganizationDetailType... its really done... but when i want to select to view data that i have been inserted, there has no data... what' wrong?
below is my command to insert
insert v_OrganizationDetailType
select 'PTM', 'Sequ Inspection', 'Cheras', '0343234321', '0343234322', 'Sequ@inspec.com', 'Oil and Gas' UNION
select 'PTM', 'Alpha', 'Bangi', '0344567654', '0344567653', 'Alpha@yahoo.com', 'Hospitality Consultant'
select * from v_OrganizationDetailType
The is no data that i have inserted
February 14, 2008 at 11:59 pm
any suggestion??
February 15, 2008 at 2:42 am
Sorry, before making my post I really did not look at your data. The problem is that you are wanting to compare the inserted data to a table that has no records.
Let's change the trigger to look like this:
insert OrganizationDetail (Org_name, Org_address, Org_tel_no, Org_fax_no, Org_email, Org_description, Type_application_id)
select inserted.Org_name,
inserted.Org_address,
inserted.Org_tel_no,
inserted.Org_fax_no,
inserted.Org_email,
inserted.Org_description,
OrganizationType.Type_application_id
from inserted inner join OrganizationType on inserted.Type_name = OrganizationType.Type_name
inner join OrganizationDetail on inserted.Org_name = OrganizationDetail.Org_name
February 15, 2008 at 6:58 am
ejoeyz_85 (2/14/2008)
i've changed the trigger command that u given to me buat when i was insert the value, i got this message Msg 512, Level 16, State 1, Procedure trg_InsertOrganization, Line 11Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, = or when the subquery is used as an expression.
The statement has been terminated.
here is my command insert...
insert v_OrganizationDetailType
select 'PTM', 'Sequ Inspection', 'Cheras', '0343234321', '0343234322', 'Sequ@inspec.com', 'Oil and Gas' UNION
select 'PTM', 'Alpha', 'Bangi', '0344567654', '0344567653', 'Alpha@yahoo.com', 'Hospitality Consultant'
This discussion has gotten off track. You keep making changes to a join, but the subquery error you're getting is from a different trigger: trg_InsertOrganization. Your post only includes the source to tr_v_OrganizationDetailType which has no subquery and is not the code producing this error.
February 15, 2008 at 7:50 am
ejoeyz_85 (2/14/2008)
i have changed using ur recommendation... but when im trying to insert values into table v_OrganizationDetailType... its really done... but when i want to select to view data that i have been inserted, there has no data... what' wrong?
the change you made earlier is preventing the insert.
from inserted inner join OrganizationType on inserted.Type_name = OrganizationType.Type_name
left join OrganizationDetail on inserted.Org_name = OrganizationDetail.Org_name
WHERE
inserted.Org_address = OrganizationDetail.Org_address and inserted.Org_tel_no = OrganizationDetail.Org_tel_no and inserted.Org_fax_no = OrganizationDetail.Org_fax_no and inserted.Org_email = OrganizationDetail.Org_email and inserted.Org_description = OrganizationDetail.Org_description AND OrganizationDetail.Org_name IS NULL
with this change, nothing will be inserted unless all fields match... in other words, the data won't be added unless it already exists in the OrganizationDetail table. the inserted.cols = OrganizationDetail.cols conditions need to be put back into the left join. this will add rows that don't exist.
February 15, 2008 at 12:45 pm
there has no data in my table after i've inserted into table v_OrganizationDetailType. Let me explain again. I've created instead of insert for both table of OrganizationType table anf OrganizationDetail table.
For my organizationtype table, i got Type_application_id as primary key and Type_name. For table organizationdetail i got Org_application_id as primary key, Org_name, Org_address, Org_tel_no, Org_fax_no, Org_email, Org_description and Type_application_id as foreign key refer to table organizationtype
this is my 1st step. i 've created table view. and see the code below
create view v_OrganizationDetailType as
select Type_name, Org_name, Org_address, Org_tel_no, Org_fax_no, Org_email, Org_description
from OrganizationType inner join OrganizationDetail on OrganizationType.Type_application_id =OrganizationDetail.Type_application_id
the 2nd step, i've created trigger. see the code below
create trigger tr_v_OrganizationDetailType on v_OrganizationDetailType instead of insert as
BEGIN
insert OrganizationType (Type_name)
select distinct inserted.Type_name
from inserted left join OrganizationType on inserted.Type_name = OrganizationType.Type_name
where OrganizationType.Type_name IS NULL /*** Exclude Organization Types already in the table ***/
insert OrganizationDetail (Org_name, Org_address, Org_tel_no, Org_fax_no, Org_email, Org_description, Type_application_id)
select distinct inserted.Org_name, inserted.Org_address, inserted.Org_tel_no,
inserted.Org_fax_no, inserted.Org_email, inserted.Org_description, OrganizationType.Type_application_id
from inserted inner join OrganizationType on inserted.Type_name = OrganizationType.Type_name
left join OrganizationDetail on inserted.Org_name = OrganizationDetail.Org_name
left join OrganizationDetail on inserted.Org_address = OrganizationDetail.Org_address
left join OrganizationDetail on inserted.Org_tel_no = OrganizationDetail.Org_tel_no
left join OrganizationDetail on inserted.Org_fax_no = OrganizationDetail.Org_fax_no
left join OrganizationDetail on inserted.Org_email = OrganizationDetail.Org_email
left join OrganizationDetail on inserted.Org_description = OrganizationDetail.Org_description
where OrganizationDetail.Org_name IS NULL /*** Exclude Organization Detail already in the table ***/
END -- trigger def
go
after execute both of codes above, i want to insert values into table v_OrganizationDetailType because instead of trigger. I've use this code to insert. see the command below
insert v_OrganizationDetailType
select 'PTM', 'Sequ Inspection', 'Cheras', '0343234321', '0343234322', 'Sequ@inspec.com', 'Oil and Gas' UNION
select 'PTM', 'Alpha', 'Bangi', '0344567654', '0344567653', 'Alpha@yahoo.com', 'Hospitality Consultant'
Then, i've executed the command and the command is succesfully. But the data i've inserted is not have in the table v_OrganizationDetailType. What going on? Anyone can help me... i hope all of u could understand for what im trying to explain....
February 15, 2008 at 1:06 pm
let's start over. the code you just posted is your from your original post which results in this error: The objects "OrganizationDetail" and "OrganizationDetail" in the FROM clause have the same exposed names.
from inserted inner join OrganizationType on inserted.Type_name = OrganizationType.Type_name
left join OrganizationDetail on inserted.Org_name = OrganizationDetail.Org_name
left join OrganizationDetail on inserted.Org_address = OrganizationDetail.Org_address
left join OrganizationDetail on inserted.Org_tel_no = OrganizationDetail.Org_tel_no
left join OrganizationDetail on inserted.Org_fax_no = OrganizationDetail.Org_fax_no
left join OrganizationDetail on inserted.Org_email = OrganizationDetail.Org_email
left join OrganizationDetail on inserted.Org_description = OrganizationDetail.Org_description
where OrganizationDetail.Org_name IS NULL /*** Exclude Organization Detail already in the table ***/
the italicized "left join OrganizationDetail on"s should be replaced with "AND"s as i suggested earlier.
after you made those changes, you were getting a different error: Subquery returned more than 1 value from the trigger trg_InsertOrganization which is on some other table.
February 16, 2008 at 4:37 am
ok i 've changed the statement that suggested from u... when im trying to insert values then i got new error as u said..
subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, = or when the subquery is used as an expression.
what next shud i do???
February 16, 2008 at 4:09 pm
ejoeyz_85 (2/16/2008)
ok i 've changed the statement that suggested from u... when im trying to insert values then i got new error as u said..subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, = or when the subquery is used as an expression.
what next shud i do???
that error is coming from trg_InsertOrganization. you need to post the source to it for someone to assist you.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply