March 20, 2009 at 10:18 pm
Hello,
I have 3 tables as follow
table 1 - Machine
MachineID, Name,Description (MachineID is Identity and Primary Key, name(not null), description(not null) )
table 2 - Brands
BrandID,Name, Description (BrandID is Identity and Primary key, name (not null), description(not null) )
table 3 - BrandsMachine
MachineID,BrandID, Name, Description (MachineID,BrandID are Primary Key, name(not null), description(not null) )
the problem is as follows....
User will insert data in machine table after that in the brands table.as the user finish entering data into the brands table i want to fire a trigger that
update the third table BrandsMachine with MachineID from Machine Table and BrandID, Name, Description from the Brands Table.
I have created a trigger it compiled but wen i insert the data into the machine table i am getting error
--- (( Msg 515, Level 16, State 2, Procedure trg_insertMachineInfo, Line 5
Cannot insert the value NULL into column 'BrandID', table 'trail.dbo.BrandsInMachine'; column does not allow nulls. INSERT fails.
The statement has been terminated. )) ---
here's the trigger
if object_id('trg_insertMachineInfo', 'tr')
Is Not Null
drop trigger trg_insertMachineInfo
go
If object_id('trg_updateBrandInfo', 'tr')
Is Not Null
drop trigger trg_updateBrandInfo
go
create trigger tr_insertmachineInfo
on Machine
After Insert
as Set NOCOUNT On
Insert into BrandsMachine(MachineID)
select MachineID from Inserted
set no count off
go
create trigger try_updateBrandInfo
on Brands
after Insert, Update
as Set NOCOUNT on
Declare @bid int
select @bid = BrandID from Inserted
Update BrandsMachine
set BrandID = @bid
where BrandID is null
Update bm
set bm.Name = i.Name
bm.Description = i.Description
from BrandsMachine bm
with (no luck)
inner join inserted i
on i.BrandID = bm.BrandID
set no count off
go
please help me! I want the data should be updated in the 3rd table. with MachineID, BrandID,Name, description (of the brands table)..help
March 21, 2009 at 2:14 am
Hello,
The statement “Insert into BrandsMachine(MachineID)” will fail because a column that participates in the PRIMARY KEY constraint cannot accept null values and you are not supplying a value for BrandID.
You could consider inserting a dummy value into this column i.e. “Insert into BrandsMachine(MachineID, 999999)”.
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
March 21, 2009 at 10:46 am
In a many-to-many relation, such as described by these three tables, the maintenance of the Machines and the Brands and the BrandsinMachines must by done separately. You can combine these functions partially, but never entirely.
For instance, when adding as new Machine, you can give the user the option to specify which Brands are in it.
Or, when adding a new Brand, you can allow users to specify which Machines it is in.
And finally, you can have a special function (not normally necessary) that would allow a user to add a new Brand and and the new Machine that it happens to be in, at the same time.
-------------------
In each of these cases however, two things hold true:
1) You still need the other functions, and
2) You cannot implement the relation with a trigger, because you need more information than is in any single table. You can, however, easily implement them with a stored procedure that has parameters for all of the necessary infomation and then splits it up to insert/update the tables appropriately.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 21, 2009 at 8:55 pm
Thanx sss.. for ur suggestion. but i have to do it with help of trigger only. so pls pls tell me what modification i have to do. pls.
March 21, 2009 at 9:51 pm
We have already answered your question. It cannot be done the way you are trying to do it.
Why do you think that you can only use a trigger?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 22, 2009 at 9:02 am
a few more thoughts on your concept / code snippets:
Declare @bid int
select @bid = BrandID from Inserted
Update BrandsMachine
set BrandID = @bid
where BrandID is null
You shouldn't store the result from the inserted table in an int variable unless you can guarantee by 100% that there will be never ever an insert statement with more than just one row. You can easily test the effect by adding two lines with a single insert statement via SSMS... Use table variable instead.
It also seems to be a strange business rule that any MachineID that is not yet associated with a BrandID will be automatically linked to the next entered BrandID... (assuming you've been able to fill the reference table in the first place like following John Marsh's suggestion).
Update bm
set bm.Name = i.Name
bm.Description = i.Description
Your table concept doesn't look normalized.
Based on your code, Name and Description of BrandsMachine always are equal to the related values in the Brands table. If this is just a default value that will be changed later on, make it different to the original table (e.g. "Default:" + name). If the values remain unchanged, drop those columns in your reference table.
with (no luck)
Sounds like a "Freudian slip"... 😉
If you're forced to do it with triggers only, there's probably more stuff to be rethought/ redesigned than just the code snippets you provided...
As already stated by Barry: It won't work the way you're trying.
March 22, 2009 at 9:25 am
Thank you for your valuable suggestion. I should look the broader aspect.
I will try it again with Stored procedure.But it will be helpful for me if i get the codes.....
Thanks !!!!!!!
March 22, 2009 at 11:08 am
gopalchettri (3/22/2009)
Thank you for your valuable suggestion. I should look the broader aspect.I will try it again with Stored procedure.But it will be helpful for me if i get the codes.....
Thanks !!!!!!!
To do this we would need a much clearer idea of what application function(s) you are trying to support or implement here. I laid out the choices in a previous reply, but you never responded to it or indicated which you were really trying to do.
For your sake I will reiterate them as simply as possible. These are the most basic application Add functions for a many-to-many relationship like yours:
1) Add New Machine: Inserts a new record in the Machine table.
2) Add New Brand: Inserts a new record in the Brands table
3) Add new association: Inserts a new record in the BrandInMachine table that associates an existing Brand record with an existing Machine record.
There are typically two more advanced "Add" functions that are often seen in applications:
4) Add Machine associations: adds multiple BrandInMachine records, associating one Machine with multiple Brands.
5) Add Brand associations: adds multiple BrandInMachine records, associating one Brand with multiple Machines.
And also two compound functions:
6) Add New Machine & associations: Combines function (1) and (4) above. Adds a new Machine record and then associates multiple Brands with it.
7) Add New Brand & associations: Combines function (2) and (5) above. Adds a new Brand record and then associates multiple Machines with it.
Now, which of these application functions do you actually need to support?
And while we are at it: What is the client or mid-tier software that will be callinf these functions or providing these new records to the database?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 22, 2009 at 9:34 pm
Well very much appreciated the help u giving me Thanks n god bless.
My problem is as follows ;
I have 3 tables name: (i) Machine where
MachineID (Primary key,Identity)
Name, Description
(ii)Brands where
BrandID (Primary , identity)
Name, description
(iii) BrandsMachine has
MachineID,BrandID, Name, Description
I have a webpage where user insert the Machine then Brand related to the Machine.
Whenever both the Machine and Brand table filled with the data, the 3rd table BrandsMachine will automatically(by any mean) will get the MachineID frm Machine table, BrandID,Name, description from the Brands Table.
I hope i have clear the concept. looking for solution thanxs
March 22, 2009 at 9:37 pm
yes like the way sir u have written ,feature with 7 no.
March 22, 2009 at 9:41 pm
More accurately there is many to many relation ship.
so table should in such a manner that
whenever a Machine is selected all the Brand of that Machine should be show as a result.
so i have made the table like that.
where BrandsMachine is a junction table.
March 22, 2009 at 9:58 pm
Hi gopalchettri ,
Can you tell me from which process you are saving the Machine and Brands table data from WEBPAGE .
Thanks
Vinit Srivastava
March 22, 2009 at 10:04 pm
ado.net. i hape m clear
March 22, 2009 at 10:44 pm
Dear ,
Please clear that you are using insert command or any stored procedure for saving the Machine and Brands data . send me the code
Thanks
Vinit Srivastava
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply