GO
CREATE TRIGGER UTR_Insert_Person
ON Person
For INSERT
AS 
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;
	END
END

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 MVP
http://visakhm.blogspot.com/

---

quote:

---

Originally posted by suriyarupa

Hi,

Try this. I hope this code will help you.
...

---

CREATE TRIGGER INSERT_CAR 
ON Person
FOR INSERT
AS 
BEGIN

INSERT INTO Fords (PersonID, [Car Name])
SELECT i.ID, 'Test Ford Addition'
FROM INSERTED i
CROSS JOIN master..spt_values v
WHERE v.type='p'
AND v.number BETWEEN 1 AND i.Qty
AND i.CarTypeID = 1

INSERT INTO Mercedes (PersonID, [Car Name])
SELECT i.ID, 'Test Merc Addition'
FROM INSERTED i
CROSS JOIN master..spt_values v
WHERE v.type='p'
AND v.number BETWEEN 1 AND i.Qty
AND i.CarTypeID = 2

END
GO