Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
Kurren
Starting Member
3 Posts |
Posted - 2013-03-18 : 06:16:54
|
HiI have simplified my situation into a small example.I have a "Person" table. For the sake of example, each person can have a number of cars, of a single type. Here are the tables:Person- PersonID- Name- CarTypeID- QtyCarType- CarTypeID- TypeNameFord- PersonIDMercedes- PersonIDThe CarType table has 2 records: (1, "Ford"), (2, "Mercedes").I need to create a trigger which executes on an INSERT into the Person table. For each record that is inserted, call it (iPersonID, iName, iCarTypeID, iQty): If iCarTypeID is 1 then add iQty number of records in the Ford table. If iCarTypeID is 2 then add iQty number of records in the Mercedes table. Each record we insert in Ford or Mercedes has a PersonID of iPersonID.I hope that example makes sense. So far I am aware that I'll need to use an IF statement to decide on which table to insert into, and a while statement to insert a record iQty number of times. However I am unsure of how to put this all together, and also how to run this for each record inserted into the person table.I am primarily a .Net developer, however am quite new to T-SQL. Any help would be greatly appreciated.Thanks,Kurren |
|
suriyarupa
Starting Member
19 Posts |
Posted - 2013-03-18 : 07:38:31
|
Hi,Try this. I hope this code will help you.CREATE TABLE Person( PersonID INT, Name VARCHAR(50), CarTypeID INT, Qty SMALLINT)CREATE TABLE Ford( PersonID INT)CREATE TABLE Mercedes( PersonID INT)---------------------------------------------------------CREATE TRIGGER INSERT_TRIGGER ON PersonFOR INSERTASBEGIN DECLARE @PersonID INT,@Name VARCHAR(50),@CarTypeID INT,@QTY INT DECLARE InsertedValues CURSOR FOR SELECT PersonID,Name,CarTypeID,Qty FROM INSERTED OPEN InsertedValues FETCH NEXT FROM InsertedValues INTO @PersonID,@Name,@CarTypeID,@Qty WHILE @@FETCH_STATUS = 0 BEGIN IF @CarTypeID = 1 BEGIN WHILE (@QTY > 0) BEGIN INSERT INTO Ford SELECT PersonID FROM INSERTED WHERE PersonID = @PersonID AND CarTypeID = 1 SET @QTY = @QTY - 1 END END IF @CarTypeID = 2 BEGIN WHILE (@QTY > 0) BEGIN INSERT INTO Mercedes SELECT PersonID FROM INSERTED WHERE PersonID = @PersonID AND CarTypeID = 2 SET @QTY = @QTY - 1 END END FETCH NEXT FROM InsertedValues INTO @PersonID,@Name,@CarTypeID,@Qty END CLOSE InsertedValues DEALLOCATE InsertedValuesENDGO |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-03-18 : 07:47:30
|
If there is no possibility of inserting multiple records into Person at a time and also there are limited number of carTypes, then you can make use of following triggerGOCREATE TRIGGER UTR_Insert_PersonON PersonFor INSERTAS BEGIN DECLARE @NoOfRecords INT, @iPersonId INT, @iName VARCHAR(5), @iCarTypeId INT ; SELECT @iPersonId = PersonID, @iName = name, @iCarTypeId = CarTypeId, @NoOfRecords = qty FROM inserted; WHILE @NoOfRecords !=0 BEGIN IF @iCarTypeId = 1 INSERT INTO Ford VALUES( @iPersonId); ELSE IF @iCarTypeId = 2 INSERT INTO Mercedes VALUES( @iPersonId); SET @NoOfRecords = @NoOfRecords-1; ENDEND --Chandu |
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2013-03-19 : 05:22:20
|
Though it shows that there is PersonID column in the "ford" and "mercedez" tables but what I perceived form the description is that you're trying to store the iQty information/value in the corresponding ford or mercedez tables. So my question is that Why do you want to store iQty of the person table in a separate table [bold]again[/bold] when you have this information already in Person table? At least make no sense to meCheersMIK |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-19 : 10:23:05
|
also even in current way the database design is not flexible. why do you have separate tables for each cartype. A much better flexible approach would be to store them in a single table CarType with fields ID,Description and Qty and will have values for all like ford,mercedes,rollsroyce etc------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
Kurren
Starting Member
3 Posts |
Posted - 2013-03-27 : 12:06:15
|
quote: Originally posted by visakh16 also even in current way the database design is not flexible. why do you have separate tables for each cartype. A much better flexible approach would be to store them in a single table CarType with fields ID,Description and Qty and will have values for all like ford,mercedes,rollsroyce etc------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
The example was a simple representation of my actual problem. This pattern is in fact necessary for my current situation. |
|
|
Kurren
Starting Member
3 Posts |
Posted - 2013-03-27 : 13:18:00
|
quote: Originally posted by suriyarupa Hi,Try this. I hope this code will help you....
Thank you very much for your reply, you helped a lot!For anyone else reading this, my create trigger was similar to the one posted by suriyarupa, with a change to the "Insert" statement. Here it is:CREATE TRIGGER INSERT_CAR ON Person FOR INSERTAS BEGIN DECLARE @ID INT,@Name nchar(10),@CarTypeID INT,@Qty INT DECLARE InsertedValues CURSOR FOR SELECT ID,Name,CarTypeID,Qty FROM INSERTED OPEN InsertedValues FETCH NEXT FROM InsertedValues INTO @ID,@Name,@CarTypeID,@Qty WHILE @@FETCH_STATUS = 0 BEGIN IF @CarTypeID = 1 BEGIN WHILE (@QTY > 0) BEGIN INSERT INTO Fords (PersonID, [Car Name]) Values (@ID, 'Test Ford Addition') SET @QTY = @QTY - 1 END END IF @CarTypeID = 2 BEGIN WHILE (@QTY > 0) BEGIN INSERT INTO Mercedes (PersonID, [Car Name]) Values (@ID, 'Test Merc Addition') SET @QTY = @QTY - 1 END END FETCH NEXT FROM InsertedValues INTO @ID,@Name,@CarTypeID,@Qty END CLOSE InsertedValues DEALLOCATE InsertedValuesENDGO |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-28 : 04:49:04
|
why do you need a cursor here? i would have just done like thisCREATE TRIGGER INSERT_CAR ON PersonFOR INSERTAS BEGININSERT INTO Fords (PersonID, [Car Name])SELECT i.ID, 'Test Ford Addition'FROM INSERTED iCROSS JOIN master..spt_values vWHERE v.type='p'AND v.number BETWEEN 1 AND i.QtyAND i.CarTypeID = 1INSERT INTO Mercedes (PersonID, [Car Name])SELECT i.ID, 'Test Merc Addition'FROM INSERTED iCROSS JOIN master..spt_values vWHERE v.type='p'AND v.number BETWEEN 1 AND i.QtyAND i.CarTypeID = 2ENDGO ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|