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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 MSSQL Trigger: Inserting multiple records

Author  Topic 

Kurren
Starting Member

3 Posts

Posted - 2013-03-18 : 06:16:54
Hi
I 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
- Qty

CarType
- CarTypeID
- TypeName

Ford
- PersonID

Mercedes
- PersonID

The 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 Person
FOR INSERT
AS
BEGIN
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 InsertedValues
END
GO
Go to Top of Page

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 trigger

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


--
Chandu
Go to Top of Page

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 me

Cheers
MIK
Go to Top of Page

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

Go to Top of Page

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





The example was a simple representation of my actual problem. This pattern is in fact necessary for my current situation.
Go to Top of Page

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 INSERT
AS
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 InsertedValues
END
GO
Go to Top of Page

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 this


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


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -