Insert into statement and triggers

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

SQL Server 2000 Forums

Transact-SQL (2000)

Insert into statement and triggers

Author

Topic

giovi2002
Starting Member

46 Posts

Posted - 2005-07-05 : 14:02:12

I'm using a trigger on a table which calculates a version number for a record. Identical records (records with same alternative key) will be given a new version number. This works great when i paste records from a excel sheet into sql.

When using an 'insert into' statement my trigger produces an error because all records are inserted at once. Within the trigger I use the 'inserted' table to check the current version number.
Is there any statement which will insert records from a query row by row? is there a way - besides cursor - to have sql insert the records row by row?

(I used a cursor as a workaround for an import of records to handle each record row by row which works great but I only would like to use a cursor for import routines).

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-07-05 : 14:28:07

Are you saying that your version number calculation calculates the same value for all records being inserted so you're getting a PK violation?

Post the trigger code. What is the error you're getting?

Be One with the Optimizer
TG

giovi2002
Starting Member

46 Posts

Posted - 2005-07-06 : 04:16:27

CREATE TRIGGER trg_versienummer_rep_aantal_rituitval ON dbo.REP_AANTAL_RITUITVAL
AFTER INSERT
AS
Select lijnnummer from buslijn
UPDATE dbo.REP_AANTAL_RITUITVAL
Set Versienummer = (Select max(versienummer)+1
From dbo.rep_aantal_rituitval
Where
systeemlijnnummer= (select systeemlijnnummer from inserted) And
datumvan=(select datumvan from inserted) And
datumtot=(select datumtot from inserted) And
sender_id=(Select sender_id from inserted)
Group by systeemlijnnummer,datumvan,datumtot,sender_id)
Where autonummer = (Select autonummer from inserted) And
Versienummer=0

The error when using insert into is something like this: subquery returns multiple values, not allowed

All I need is an insert statement which inserts row by row instead of inserting recordsets

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-07-06 : 12:00:18

The problem you're having is caused by a fundamental database design flaw. You want the column versienummer to behave like an identity column but to reset itself to 1 based on other column groupings. This is almost always an unnecessary requirement.

However, people that use that design are rarely convinced that they should re-design. If you are one of those people then you're right, the most straight forward way to do this is to perform the update one record at a time. So you should loop through the inserted table and perform a seperate update (as required) for each record in the inserted table.

Be One with the Optimizer
TG

giovi2002
Starting Member

46 Posts

Posted - 2005-07-20 : 15:19:39

quote:
Originally posted by TG

So you should loop through the inserted table and perform a seperate update (as required) for each record in the inserted table.

Be One with the Optimizer
TG

Hi TG, because the insert into refers to an import table it was easy for me to design a cursor procedure. In this way it isn't really ugly.

I have another table with calculations where i don't use a help table but should define one which is really ugly in modelling terms. I'm going to try the insert statement instead of insert into, maybe that will help. According to BOL insert is used for each row, insert into for recordsets

giovi2002
Starting Member

46 Posts

Posted - 2005-07-20 : 15:26:32

Text underneath is copied from BOL

Multirow Considerations
An important consideration to keep in mind when writing the code for a trigger is that the statement that causes the trigger to fire can be a single statement that affects multiple rows of data, rather than a single row. This is common for UPDATE and DELETE triggers because these statements often affect multiple rows. It is less common for INSERT triggers, because the basic INSERT statement adds only a single row. However, because an INSERT trigger can be fired by an INSERT INTO (table_name) SELECT statement, the insertion of many rows may result in a single trigger invocation.

Multirow considerations are particularly important when the function of a trigger is to automatically recalculate summary values from one table and store the results in another for ongoing tallies.

Note The use of cursors in triggers is not recommended because of the potentially negative impact on performance. Use rowset-based logic rather than cursors to design a trigger that affects multiple rows.

Examples
The triggers in the following examples are designed to store a running total of a column in another table.

A. Store a running total for a single-row insert
The first version of the trigger works well for a single-row insert, when a row of data is loaded into the sales table. An INSERT statement fires the trigger, and the new row is loaded into the inserted table for the duration of the trigger execution. The UPDATE statement reads the qty column value for the row and adds it to the existing value in the ytd_sales column in the titles table. The WHERE clause ensures that the updated row in the sales table matches the title_id of the row in the inserted table.

-- Trigger is valid for single-row inserts.
CREATE TRIGGER intrig
ON sales
AFTER INSERT AS

UPDATE titles
SET ytd_sales = ytd_sales + qty
FROM inserted
WHERE titles.title_id = inserted.title_id

B. Store a running total for a multirow or single row insert
In the case of a multirow insert, the trigger in Example A might not operate correctly; the expression to the right of an assignment expression in an UPDATE statement (ytd_sales + qty) can be only a single value, not a list of values. So the effect of the trigger is to obtain a value from any single row in the inserted table and add it to the existing ytd_sales value in the titles table for a given title_id value. This might not have the desired effect if a single title_id value occurred more than once in the inserted table.

To update the titles table properly, the trigger has to accommodate the possibility of multiple rows in the inserted table. This can be done with the SUM function that calculates the total qty for a group of rows in the inserted table for each title_id. The SUM function is placed in a correlated subquery (the SELECT statement in parentheses), which returns a single value for each title_id in the inserted table that matches or is correlated with a title_id in the titles table.

-- Trigger is valid for multirow and single-row inserts.
CREATE TRIGGER intrig
ON sales
AFTER INSERT AS

UPDATE titles
SET ytd_sales = ytd_sales +
(SELECT SUM(qty) -- Correlated subquery.
FROM inserted
WHERE titles.title_id = inserted.title_id)
WHERE titles.title_id IN
(SELECT title_id FROM inserted)

This trigger also works correctly in a single-row insert; the sum of the qty value column is the sum of a single row. However, with this trigger the correlated subquery and the IN operator used in the WHERE clause require additional processing from Microsoft® SQL Server™ 2000, which is unnecessary for a single-row insert.

C. Store a running total based on the type of insert
You can change the trigger to use the method optimal for the number of rows. For example, the @@ROWCOUNT function can be used in the logic of the trigger to distinguish between a single and a multirow insert.

-- Trigger valid for multirow and single row inserts
-- and optimal for single row inserts.
CREATE TRIGGER intrig
ON sales
FOR INSERT AS
IF @@ROWCOUNT = 1
BEGIN
UPDATE titles
SET ytd_sales = ytd_sales + qty
FROM inserted
WHERE titles.title_id = inserted.title_id
END
ELSE
BEGIN
UPDATE titles
SET ytd_sales = ytd_sales +
(SELECT SUM(qty)
FROM inserted
WHERE titles.title_id = inserted.title_id)
WHERE titles.title_id IN
(SELECT title_id FROM inserted)
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources