Inserted row deletes after trigger

I'm hoping someone has seen this before because I have no idea what could be causing it.

I have an SQL 2005 database with multiple tables and several triggers on the various tables all set to run after insert and update.

My program inserts a record into the "items" via a SP that returns the index of the newly added row. The program then inserts a row into another table that is related to items. When the row is inserted into the second table it gets an error that it cannot insert the record because of a foreign key restraint. Checking the items table shows the record that was just inserted in there is now deleted.

The items record is only deleted when I have my trigger on that table enabled. Here is the text of the trigger:

GO
SETANSI_NULLSON
GO
SETQUOTED_IDENTIFIERON
GO

ALTERTRIGGER [dbo].[TestTrigger]
ON [dbo].[items]
AFTERINSERT

AS
BEGIN

SETNOCOUNTON;

INSERTINTO tblHistory(table_name, record_id, is_insert)
VALUES('items', 123, 1)

END

tblHistory's field types are (varchar(50), BigInt, bit).

As you can see there is nothing in the trigger to cause the items record to be deleted, so I have no idea what it could be? Anyone ever see this before?

Thanks in advance!

Hey,

I don't know that the row is deleted, but that the row doesn't get actually inserted for some reason. What do the two insertions look like? In SQL or ADO.NET code? Could it be that the first item doesn't get inserted, then returns a number that doesn't match an entry in that table, and that is why you get an error for the second insert?

|||

No, the first item is inserted and the returned value is exactly what it should be. When we test it without the trigger enabled and it all works, the new primary key value is the next value after the one that disapeared (i.e. if the record that was deleted was 5 the next one that works is 6).

|||

Are you using @.@.identity?

|||

If one of the follow on triggers fails, for whatever reason, the insert statement will be rolled back.

I suggest commenting out the triggers one by one (from last run to first run) until you figure out which one is the problem.

(Or learn to use the debugger in sql server.)

|||

David is correct in that the trigger code is considered part of the insert transaction.

If the trigger fails, then the entire "transaction" is rolled back, including the insert. If tblHistory has a foreign key constraint, and the trigger fails because of it, then you will get exactly what you are describing. The record is inserted partially (uncommitted), the trigger is fired, an error is encountered, then the insert is rolled back and the error from the trigger is sent to the client.

|||

It couldn't have been the trigger failing, b/c there are no constraints on the history table and while yo uare correct the error would have been returned as if it was coming from the insert statement I said above "When the row is inserted into the second table it gets an error that it cannot insert the record because of a foreign key restraint." The error was not about hte history table.

Motley you actually had the answer. What was happening is the stored procedure ran and inserted the row into items, the trigger ran on that and inserted the row into web updates, the stored procedure then returned the @.@.Identity, but since that returns the last identity of any insert to the database it was returning the identity of the history table, not the items table. When the second insert was run it was trying to insert the wrong identity and failed the foreign key restraint, resulting in the entire transaction to fail and rollback, giving the appearance the items record had been deleted.

Thanks for your help!

|||

Use scope_identity(), not identity! scope_identity was created to avoid just this problem!

sql

Inserted and deleted temp tables ?

I want to know how inserted and deleted temp tables in SQL server work. My question is more regarding how they work when multiple users accessing the same database. Suppose two users update the database at the same time. In that case what are the values stored in the inserted and deleted tables.

I have a trigger that records changes to the database as in an audit trail. Like any other audit trail I insert data into my audit table from the inserted and deleted temp tables in MS SQL Server. I however am not clear as to how these inserted and deleted tables store values when two users update the database at the same time. Are there separate inserted and deleted tables for each session. The users access the database thru ASP pages.

The audit trail I am trying to use is http://www.nigelrivett.net/AuditTrailTrigger.html

I actually would like to store the inserted and deleted temp tables into other temporary tables so that I can access these tables thru a stored procedure. This is when the problem of same users updating the temporary tables is more pronounced.

Thanks in advance.If memory serves, the INSERTED and DELETED tables are special objects that belong to each session. So if two users update at the same time you should have two copies of these virtual tables

User1INSERTED/DELETED
User2INSERTED/DELETED

Brent

Insert/Updated SP from multiple tables

How do I insert unrelated statistical data from three tables into another
table that already exist with data using an insert or update stored procedure?
OR...
How do I write an insert/Update stored procedure that has multiple select
and a where something = something statements?

This is what I have so far and it do and insert and does work and I have no idea where to begin to do an update stored procedure like this...

CREATE PROCEDURE AddDrawStats
AS
INSERT Drawing (WinnersWon,TicketsPlayed,Players,RegisterPlayers)

SELECT
WinnersWon = (SELECT Count(*) FROM Winner W INNER JOIN DrawSetting DS ON W.DrawingID = DS.CurrentDrawing WHERE W.DrawingID = DS.CurrentDrawing),

TicketsPlayed = (SELECT Count(*) FROM Ticket T INNER JOIN Student S ON T.AccountID = S.AccountID WHERE T.Active = 1),

Players = (SELECT Count(*) FROM Ticket T INNER JOIN Student S ON T.AccountID = S.AccountID WHERE T.AccountID = S.AccountID ),

RegisterPlayers = (SELECT Count(*) FROM Student S WHERE S.AccountID = S.AccountID )

FROM DrawSetting DS INNER JOIN Drawing D ON DS.CurrentDrawing = D.DrawingID

WHERE D.DrawingID = DS.CurrentDrawing
GO"INNER JOIN DrawSetting DS ON W.DrawingID = DS.CurrentDrawing"
and
"WHERE W.DrawingID = DS.CurrentDrawing"
are redundant. They both accomplish the same thing; associating records in the two tables. Among SQL Server DBAs, the INNER JOIN syntax is preferred, so drop the links in your WHERE clauses.

As to your other issues, I'm sorry but the SQL statement you posted is too disjointed to figure out what your intentions are. You will need to describe your tables and your objective if you want more help, but embedding subqueries into the SELECT clause is rarely a good idea. I highly suspect that what your SQL statement describes is not really what you are trying to do.|||yes my attention is that I have Four related/non-related table and I would like to get some statistical data such as the count of how many student are in the student table, how many student are playing the current drawing, how many tickets are in the current drawing, and how many students won the current drawing. Setting up a common inner join would not allow me to get the exact data I need. Plus, I need to insert this data in the drawing table record that already have data but these fields are null. My stored procedure works somewhat, but it creates a new record; I want the stored procedure to insert this information in the record that already exist where drawing = the CurrentDrawing. So should I do an insert/update stored procedure, and how? All I need to see is an example of a stored procedure that insert or update data in some table where some criteria are met which comes from multiple select statements using different table within those select statement.|||This is what your SQL Statement describes, but again, I doubt that it is exactly what you want:

CREATE PROCEDURE AddDrawStats
AS

Declare @.Players int
Declare @.RegisterPlayers int
Declare @.TicketsPlayed int

set @.Players = (SELECT Count(*) FROM Ticket T INNER JOIN Student S ON T.AccountID = S.AccountID)
set @.RegisterPlayers = (SELECT Count(*) FROM Student)
set @.TicketsPlayed = (SELECT Count(*) FROM Ticket T INNER JOIN Student S ON T.AccountID = S.AccountID WHERE T.Active = 1),

Update Drawing
set WinnersWon = WinnersSubquery.WinnersWon,
TicketsPlayed = @.TicketsPlayed,
Players = @.Players,
RegisterPlayers = @.RegisterPlayers
from Drawing
inner join
(SELECT DS.CurrentDrawing, count(*) as WinnersWon
FROM DrawSetting DS
INNER JOIN Winner W on DS.CurrentDrawing = w.DrawingID
GROUP BY DS.CurrentDrawing) WinnersSubquery
on Drawing.DrawingID = WinnersSubquery.CurrentDrawing

INSERT INTO Drawing (DrawingID, WinnersWon, TicketsPlayed, Players, RegisterPlayers)
select WinnersSubquery.CurrentDrawing,
WinnersSubquery.WinnersWon,
@.TicketsPlayed,
@.Players,
@.RegisterPlayers
from (SELECT DS.CurrentDrawing, count(*) as WinnersWon
FROM DrawSetting DS
INNER JOIN Winner W on DS.CurrentDrawing = w.DrawingID
GROUP BY DS.CurrentDrawing) WinnersSubquery
left outer join Drawing on WinnersSubquery.CurrentDrawing = Drawing.DrawingID
where Drawing.DrawingID is null|||Thanks for all the help! This works but I have two questions?
Could I have written this Stored procedure better? and...
Why this statement yeilds the wrong results? *i.e.*each player can have up to five tickets in the ticket table, but this statement count each ticket as a player.How do I write this statement to get only unigue AccountID within the ticket table?
**SET @.Players = (SELECT Count(*) FROM Ticket T INNER JOIN Student S ON T.AccountID = S.AccountID)

CREATE PROCEDURE AddDrawStats2
AS

DECLARE @.WinnersWon INT
DECLARE @.TicketsPlayed INT
DECLARE @.Players INT
DECLARE @.RegisterPlayers INT

SET @.WinnersWon = (SELECT COUNT(*) FROM Winner W INNER JOIN DrawSetting DS ON W.DrawingID = DS.CurrentDrawing)
SET @.TicketsPlayed = (SELECT COUNT(*) FROM Ticket T WHERE T.Active = 1)
SET @.Players = (SELECT Count(*) FROM Ticket T INNER JOIN Student S ON T.AccountID = S.AccountID)
SET @.RegisterPlayers = (SELECT COUNT(*) FROM Student )

UPDATE Drawing
SET WinnersWon = @.WinnersWon,
TicketsPlayed = @.TicketsPlayed,
Players = @.Players,
RegisterPlayers = @.RegisterPlayers

WHERE DrawingID = (SELECT CurrentDrawing FROM DrawSetting)
GO|||SET @.Players = (SELECT Count(Distinct T.AccountID) FROM Ticket T INNER JOIN Student S ON T.AccountID = S.AccountID)|||Thanks so much for all the help. This stored procedure does the job, but you see any drawbacks?

CREATE PROCEDURE AddDrawStats
AS
DECLARE @.WinnersWon INT
DECLARE @.TicketsPlayed INT
DECLARE @.Players INT
DECLARE @.RegisterPlayers INT

SET @.WinnersWon = (SELECT COUNT(*) FROM Winner W INNER JOIN DrawSetting DS ON W.DrawingID = DS.CurrentDrawing)
SET @.TicketsPlayed = (SELECT COUNT(*) FROM Ticket T WHERE T.Active = 1)
SET @.Players =(SELECT Count(Distinct T.AccountID) FROM Ticket T WHERE T.Active = 1)
SET @.RegisterPlayers = (SELECT COUNT(*) FROM Student )

UPDATE Drawing
SET
WinnersWon = @.WinnersWon,
TicketsPlayed = @.TicketsPlayed,
Players = @.Players,
RegisterPlayers = @.RegisterPlayers
WHERE DrawingID = (SELECT CurrentDrawing FROM DrawSetting)
GO

Insert with multiple tables

Hi All SQLStars

I am kind of new to developing complex databases in SQL Server 2005, so I hope there is someone out there with the answer to all my prayers.

I have 3 distinct tables

tblContainer

ContNum smallint (primary key)

Length smallint

Stop smallint

ContTypeID tinyint

HookUpID tinyint

tblContTypes

ContTypeID tinyint

ContType nvarchar(30)

tblHookUp

HookUpID tinyint

HookUp nvarchar(30)

I need to make an Insert query (and an update query) as stored procedures that can insert/update rows in the tblContainer, but I want the user not to enter ContTypeID or HookUpID but rather ContType and HookUp. Further there are some conditions to be validated:

WHEN ContNum BETWEEN 100 AND 199 THEN HookUpID ='13'

WHEN ContNum BETWEEN 1000 AND 1999 THEN HookUpID ='1'

WHEN ContNum BETWEEN 2000 AND 2499 THEN HookUpID ='2'

WHEN ContNum BETWEEN 2500 AND 2999 THEN HookUpID ='3'

WHEN ContNum BETWEEN 3000 AND 3249 THEN HookUpID ='4'

WHEN ContNum BETWEEN 3250 AND 3499 THEN HookUpID ='5'

WHEN ContNum BETWEEN 3500 AND 3749 THEN HookUpID ='6'

WHEN ContNum BETWEEN 3750 AND 3999 THEN HookUpID ='7'

WHEN ContNum BETWEEN 4000 AND 4249 THEN HookUpID ='8'

WHEN ContNum BETWEEN 4250 AND 4449 THEN HookUpID ='9'

WHEN ContNum BETWEEN 4500 AND 4749 THEN HookUpID ='10'

WHEN ContNum BETWEEN 4750 AND 4999 THEN HookUpID ='11'

WHEN ContNum BETWEEN 5000 AND 5249 THEN HookUpID ='12'

ContNum can only be a number in the above used intervals, there's a constraint on tblContainer to ensure that.

I've tried this code, but obviously it doesn't work, can you tell me why?

CREATE PROCEDURE usp_InsertContainer

(

@.ContNum smallint,

@.Length smallint,

@.Stop smallint,

@.ContType nvarchar(30),

@.HookUp nvarchar(30)

)

INSERT INTO tblContainer (ContNum, Length, Stop, ContTypeID, HookUpID)

WHEN ContNum BETWEEN 100 AND 199 THEN HookUpID ='13'

WHEN ContNum BETWEEN 1000 AND 1999 THEN HookUpID ='1'

WHEN ContNum BETWEEN 2000 AND 2499 THEN HookUpID ='2'

WHEN ContNum BETWEEN 2500 AND 2999 THEN HookUpID ='3'

WHEN ContNum BETWEEN 3000 AND 3249 THEN HookUpID ='4'

WHEN ContNum BETWEEN 3250 AND 3499 THEN HookUpID ='5'

WHEN ContNum BETWEEN 3500 AND 3749 THEN HookUpID ='6'

WHEN ContNum BETWEEN 3750 AND 3999 THEN HookUpID ='7'

WHEN ContNum BETWEEN 4000 AND 4249 THEN HookUpID ='8'

WHEN ContNum BETWEEN 4250 AND 4449 THEN HookUpID ='9'

WHEN ContNum BETWEEN 4500 AND 4749 THEN HookUpID ='10'

WHEN ContNum BETWEEN 4750 AND 4999 THEN HookUpID ='11'

WHEN ContNum BETWEEN 5000 AND 5249 THEN HookUpID ='12'

INNER JOIN tblHookUp on tblContainer.HookUpID=tblHookUp.HookUpID

VALUES(@.ContNum, @.Length, @.Stop, @.HookUp)

The result should look like this:

ContNum, Length, Stop, ContType, HookUp

1, 100, 15, Very Large, Wire

I'd really appriciate your help in this riddle - thanks a bundle.

Kind regards

Tina Nielsen

Denmark

Hi Tina,

Use a "select" statement as the source for the "insert" one.

INSERT INTO dbo.tblContainer (ContNum, Length, Stop, ContTypeID, HookUpID)

select

@.ContNum,

@.Length,

@.Stop,

(select ContTypeID from dbo.tblContTypes where ContType = @.ContType),

case

WHEN @.ContNum BETWEEN 100 AND 199 THEN HookUpID ='13'

WHEN @.ContNum BETWEEN 1000 AND 1999 THEN HookUpID ='1'

WHEN @.ContNum BETWEEN 2000 AND 2499 THEN HookUpID ='2'

WHEN @.ContNum BETWEEN 2500 AND 2999 THEN HookUpID ='3'

WHEN @.ContNum BETWEEN 3000 AND 3249 THEN HookUpID ='4'

WHEN @.ContNum BETWEEN 3250 AND 3499 THEN HookUpID ='5'

WHEN @.ContNum BETWEEN 3500 AND 3749 THEN HookUpID ='6'

WHEN @.ContNum BETWEEN 3750 AND 3999 THEN HookUpID ='7'

WHEN @.ContNum BETWEEN 4000 AND 4249 THEN HookUpID ='8'

WHEN @.ContNum BETWEEN 4250 AND 4449 THEN HookUpID ='9'

WHEN @.ContNum BETWEEN 4500 AND 4749 THEN HookUpID ='10'

WHEN @.ContNum BETWEEN 4750 AND 4999 THEN HookUpID ='11'

WHEN @.ContNum BETWEEN 5000 AND 5249 THEN HookUpID ='12'

else null

end

What about adding columns [ContNum_From] and [ContNum_To] to table [tblHookUp], and using:

INSERT INTO dbo.tblContainer (ContNum, Length, Stop, ContTypeID, HookUpID)

select

@.ContNum,

@.Length,

@.Stop,

(select ContTypeID from dbo.tblContTypes where ContType = @.ContType),

(select HookUpID from dbo.tblHookUp where @.ContNum between [ContNum_From] and [ContNum_To])

AMB

|||

Hi AMB

Thank you very, very much for your rapid reply. I'm quite sure this will solve my problem.

Just to clarify, can I use this as a template for an update and delete query as well?

Again thank very much for helping me out.

Kind regards

Tina Nielsen

Denmark

|||

Hi, Tina!

Another option is to use computed column instead of storing data in Database:

Code Snippet

CREATETABLE tblContainer (

ContNum smallintPRIMARYKEY,

Length smallint,

Stop smallint,

ContTypeID tinyint,

HookUpID AS(CASE

WHEN ContNum BETWEEN 100 AND 199 THEN 13

WHEN ContNum BETWEEN 1000 AND 1999 THEN 1

WHEN ContNum BETWEEN 2000 AND 2499 THEN 2

WHEN ContNum BETWEEN 2500 AND 2999 THEN 3

WHEN ContNum BETWEEN 3000 AND 3249 THEN 4

WHEN ContNum BETWEEN 3250 AND 3499 THEN 5

WHEN ContNum BETWEEN 3500 AND 3749 THEN 6

WHEN ContNum BETWEEN 3750 AND 3999 THEN 7

WHEN ContNum BETWEEN 4000 AND 4249 THEN 8

WHEN ContNum BETWEEN 4250 AND 4449 THEN 9

WHEN ContNum BETWEEN 4500 AND 4749 THEN 10

WHEN ContNum BETWEEN 4750 AND 4999 THEN 11

WHEN ContNum BETWEEN 5000 AND 5249 THEN 12

END)

)

So, whenever you insert or update [ContNum] column, in SELECT queries value of HookUpID is calculated automatically. You don't need to store it in database and use additional tricks during update operation.

NOTE, that this option is applicable only if SELECT statement execution speed is not critical! As I know, computed columns affect SELECT statement performance. Although, in this case, when rows count in the table is not much, it's not significant.

|||

Hi Tina,

For a "delete" you just need the PK to be deleted, and for "update", well it depends if you will be updating the PK also.

declare @.ContNum

set @.ContNum = 10

delete from

where ContNum = @.ContNum

go

declare @.ContNum int, @.Length int, @.Stop int

set @.ContNum = 10

set @.Length = 20

set @.Stop = 30

update

dbo.tblContainer

set

Length = @.Length,

Stop = @.Stop

where

ContNum = @.ContNum

go

AMB

insert with multiple subqueries

Hi

what i like to do is insert in one table 2 value from 2 different row.

exp:

table1: person

id name

1 bob

2 john

so id like to make an insert that will result in this

table 2: person_knowed

idperson1: 1

idperson2: 2

so the wuery should look something like this:

insert into person_knowed(idperson1, idperson2)
(select id from personwhere name = 'bob',
select id from personwhere name = 'john'));

anybody have an idea of how to acheive this?

insert into person_knowed(idperson1, idperson2)
select A.id, B.id from
(select id from personwhere name = 'bob')A,
(select id from personwhere name = 'john')B

This will work as you are expecting it to work only if the subqueries return 1 row each. If they return more than 1 row, you will end up with a cross-join

Insert using multiple field terminators

Hi all,
In Oracle, SQL Loader allows you to use the statement below:
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '|'
Is there a way to do this with BULK INSERT or some other import function?
The data files that I am attempting to import has fields terminated by commas
but when some of those text fields may contain a comma, they are also
enclosed by pipes. The above statement in Oracle deals with this issue
effectively... hoping I can find something for MS SQL Server. Thanks!
PatrickPatrick
CREATE TABLE TmpStList
(
stFName varchar (10) NOT NULL,
stLName varchar (10) NOT NULL,
stEmail varchar (30) NOT NULL
)
go
The data file (hawk.dat):
"Kelly","Reynold","kelly@.reynold.com"
"John","Smith","bill@.smith.com"
"Sara","Parker","sara@.parker.com"
The format file (hawk.bcp):
8.0
4
1 SQLCHAR 0 1 "\"" 0 first_quote ""
2 SQLCHAR 0 10 "\",\"" 1 stFName ""
3 SQLCHAR 0 10 "\",\"" 2 stLName ""
4 SQLCHAR 0 30 "\"\r\n" 3 stEmail ""
bulk insert TmpStList from 'C:\Staging\hawk.dat'
with (formatfile = 'C:\Staging\hawk.bcp')
select * from TmpStList
stFName stLName stEmail
-- -- --
Kelly Reynold kelly@.reynold.com
John Smith bill@.smith.com
Sara Parker sara@.parker.com
drop table TmpStList
"Patrick" <Patrick@.discussions.microsoft.com> wrote in message
news:CA167741-DE9E-4752-AEE1-97D3A67C3837@.microsoft.com...
> Hi all,
> In Oracle, SQL Loader allows you to use the statement below:
> FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '|'
> Is there a way to do this with BULK INSERT or some other import function?
> The data files that I am attempting to import has fields terminated by
> commas
> but when some of those text fields may contain a comma, they are also
> enclosed by pipes. The above statement in Oracle deals with this issue
> effectively... hoping I can find something for MS SQL Server. Thanks!
> Patrick
>

Insert Trigger and Bulk Insert

I am using Bulk Insert to insert multiple rows into a table (Invoice_Lines_Temp). I need to update/insert rows in another table (Invoice_Lines) as these rows are inserted into Invoice_Lines_Temp. As I understand, the trigger is only fired once for the insert so only one row is affected in Invoice_Lines, and this is what I'm seeing.

How would someone modify this trigger to update/insert the Invoice_Line table for each record inserted into Invoice_Line_Temp?

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

-- =============================================

-- Author: <Author,,Name>

-- Create date: <Create Date,,>

-- Description: <Description,,>

-- =============================================

ALTER TRIGGER [dbo].[Invoice_Line_Temp_Insert]

on [dbo].[Invoice_Line_Temp]

for Insert

As

Declare @.Transaction_ID int,

@.Line_Number int,

@.Item_Desc varchar(32),

@.Invoice_Date datetime,

@.Group_Number int,

@.Item_Code varchar(8),

@.Quantity_Sold decimal,

@.Section_Number int,

@.LastUpdate datetime

Select @.Transaction_ID = inserted.Transaction_ID,

@.Line_Number = inserted.Line_Number,

@.Item_Desc = Inserted.Item_Desc,

@.Invoice_Date = inserted.Invoice_Date,

@.Group_Number = inserted.Group_Number,

@.Item_Code = inserted.Item_Code,

@.Quantity_Sold = inserted.Quantity_Sold,

@.Section_Number = inserted.Section_Number,

@.LastUpdate = inserted.LastUpdate

from inserted

IF Exists

(Select dbo.invoice_line.transaction_id, dbo.invoice_line.line_number

from dbo.invoice_line

where dbo.invoice_line.transaction_id = @.Transaction_id

and dbo.invoice_line.Line_Number = @.Line_Number)

Begin

update dbo.invoice_line

set Item_Desc = @.Item_Desc,

Invoice_Date = @.Invoice_Date,

Group_Number = @.Group_Number,

Item_Code = @.Item_Code,

Quantity_Sold = @.Quantity_Sold,

Section_Number = @.Section_Number,

LastUpdate = @.LastUpdate

where dbo.invoice_line.transaction_id = @.Transaction_id

and dbo.invoice_line.Line_Number = @.Line_Number

end

ELSE

Begin

INSERT INTO dbo.Invoice_Line

(InvoiceLineId,Transaction_ID,Line_Number,Item_Desc,Invoice_Date,

Group_Number,Item_Code,Quantity_Sold,Section_Number,LastUpdate)

VALUES

(newid(),@.Transaction_ID,@.Line_Number,@.Item_Desc,@.Invoice_Date,

@.Group_Number,@.Item_Code,@.Quantity_Sold,@.Section_Number,@.LastUpdate)

end

Try this:

Code Snippet

ALTER TRIGGER [dbo].[Invoice_Line_Temp_Insert]

on [dbo].[Invoice_Line_Temp]

for Insert

As

BEGIN

UPDATE il

SET Item_Desc = i.Item_Desc,

Invoice_Date = i.Invoice_Date,

Group_Number = i.Group_Number,

Item_Code = i.Item_Code,

Quantity_Sold = i.Quantity_Sold,

Section_Number = i.Section_Number,

LastUpdate = i.LastUpdate

FROM dbo.invoice_line il

INNER JOIN inserted

ON il.transaction_id = i.Transaction_id

AND il.Line_Number = i.Line_Number

INSERT INTO dbo.Invoice_Line

(InvoiceLineId,Transaction_ID,Line_Number,Item_Desc,Invoice_Date,

Group_Number,Item_Code,Quantity_Sold,Section_Number,LastUpdate)

SELECT newid(), i.Transaction_id, i.Line_number, i.Item_Desc, i.Invoice_Date,

i.GroupNumber, i.Item_Code, i.Quantity_Sold, i.Section_Number, i.LastUpdate

FROM inserted

WHERE NOT EXISTS

( SELECT * FROM dbo.Invoice_Line il

WHERE il.transaction_id = i.Transaction_id

AND il.Line_Number = i.Line_Number)

END

|||Worked great! More elegant as well. thanks...

Insert statement with multiple select statements

hi

first of all is it possible? if so, what am i doing wrong with this

INSERT into TB2

(

ClientCode,
EngagementCode,
EngagementDescription

)

SELECT
(SELECT dbo.tarCustomer.CustID
FROM dbo.tPA00175 INNER JOIN
dbo.tarCustomer ON dbo.tPA00175.CustKey = dbo.tarCustomer.CustKey INNER JOIN
dbo.tPA00007 ON dbo.tPA00175.intJobKey = dbo.tPA00007.intJobKey),

NULL,

SELECT
(SELECT dbo.tPA00175.chrJobNumber
FROM dbo.tPA00175 INNER JOIN
dbo.tarCustomer ON dbo.tPA00175.CustKey = dbo.tarCustomer.CustKey INNER JOIN
dbo.tPA00007 ON dbo.tPA00175.intJobKey = dbo.tPA00007.intJobKey)

the first select statement for works fine, but the second one and all after i get a syntax error near 'select'.

this is just a shortened version of the statement. how would i run select statements for a table to be inserted into with different column names. also with items that are hard coded like the 'null'. thanks

tiborINSERT
into TB2
( ClientCode
, EngagementCode
, EngagementDescription )
SELECT dbo.tarCustomer.CustID
, NULL
, dbo.tPA00175.chrJobNumber
FROM dbo.tPA00175
INNER
JOIN dbo.tarCustomer
ON dbo.tarCustomer.CustKey = dbo.tPA00175.CustKey
INNER
JOIN dbo.tPA00007
ON dbo.tPA00007.intJobKey = dbo.tPA00175.intJobKey|||Thanks alot.. Something so simple but yet such a pain.

have a good one|||Ok the syntax works great but its not doing the insert. i built the select statement with a view so i know all the links are nice and strong and such. it doesnt seem to like the 'from' i think. any suggestions?|||Are you getting any error messages? I don't see anything wrong with r937's code (other than the goofy formatting ;) ).
If you run just the SELECT portion without the insert, does it return records? Are there any constraints on the target table that would prevent inserts (unique indexes, foreign keys...)?|||i apologize for not paying attention. the second part of my select statement had a join that returned nothing so i had to alter that. now its working but im getting the String or binary data would be truncated error. After i figure out which field is causing that, it should be all good. but stay tuned in case something else comes up, haha.

tibor

Insert single Parent and multiple Children

I am working on a project where I have a page that will have a parent record (Product) and then 1 or more children (options available for the product, user enters text to define) displayed in a table/gridview. There is a relationship defined in the database between the product and options table).

My question is how can I allow the user to add the product info and then within the same page also add the options and only then save it all? The options will added to a table.

Thanks for any help

You may use triggers, I mean you can create triggers on the main table, in which you can insert records to other tables. You can start from here:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_08_4nxu.asp

|||I don't see how a trigger will help. I need to have a way to add the parent and all its children at once. How will a trigger help?

Insert records in multiple tables via store proc

Any help will be appreacited
I need to insert records into multiple tables via store proc. I wrote a query statement that does that, but I need to carry one value to the next piece of the script, which is easy via query analizer, but I do not know how to pass that value to the next step in the store proc. Please see the query I am using to give me some light. The case sample is 9731285 and needs to be carry out to each step in the store proc. Thank you!
DECLARE @.Casenumber as char(20
SET @.CASENUMBER = '9731285
INSERT INTO tblCaseDat
(CaseNumber, DisplayCaseNumber
VALUES (@.CASENUMBER, (left(@.casenumber, 2))+'-'+rtrim(Right(@.casenumber,18))
G
declare @.casenumber char(20
select @.casenumber = '9731285
INSERT INTO tblname (longname
values (@.casenumber+' '+ 'Debtor1'
g
declare @.casenumber char (20
select @.casenumber = '9731285
INSERT INTO tblCasename (caseid, NameID, NameTypeID
(select caseid, (Select NameI
from tblnam
where longname =(@.casenumber+' '+ 'Debtor1')), '5
from tblcasedat
where casenumber = @.casenumber
G
declare @.casenumber char(20
select @.casenumber = '9731285
INSERT INTO tblname (longname
values (@.casenumber+' '+ 'Debtor2'
g
declare @.casenumber char (20
select @.casenumber = '9731285
INSERT INTO tblCasename (caseid, NameID, NameTypeID
(select caseid, (Select NameI
from tblnam
where longname =(@.casenumber+' '+ 'Debtor2')), '6
from tblcasedat
where casenumber = @.casenumber
Gthe batch separator (GO) resets any variable declarations.
hence, if you remove the 'GO'
remove the additional DECLARE / SET CaseNumber,
you can execution the entire set of statments as one
batch, which can be put into a stored proc,
also, an explicit BEGIN TRAN , COMMIT TRAN around the
entire set of inserts statements is probably warranted
>--Original Message--
>Any help will be appreacited.
>I need to insert records into multiple tables via store
proc. I wrote a query statement that does that, but I need
to carry one value to the next piece of the script, which
is easy via query analizer, but I do not know how to pass
that value to the next step in the store proc. Please see
the query I am using to give me some light. The case
sample is 9731285 and needs to be carry out to each step
in the store proc. Thank you!!
>DECLARE @.Casenumber as char(20)
>SET @.CASENUMBER = '9731285'
>INSERT INTO tblCaseData
> (CaseNumber, DisplayCaseNumber)
>VALUES (@.CASENUMBER, (left(@.casenumber, 2))+'-'+rtrim
(Right(@.casenumber,18)))
>GO
>declare @.casenumber char(20)
>select @.casenumber = '9731285'
>INSERT INTO tblname (longname)
> values (@.casenumber+' '+ 'Debtor1')
>go
>declare @.casenumber char (20)
>select @.casenumber = '9731285'
>INSERT INTO tblCasename (caseid, NameID, NameTypeID)
> (select caseid, (Select NameID
> from tblname
> where longname =(@.casenumber+' '+ 'Debtor1')), '5'
> from tblcasedata
> where casenumber = @.casenumber)
>GO
>declare @.casenumber char(20)
>select @.casenumber = '9731285'
>INSERT INTO tblname (longname)
> values (@.casenumber+' '+ 'Debtor2')
>go
>declare @.casenumber char (20)
>select @.casenumber = '9731285'
>INSERT INTO tblCasename (caseid, NameID, NameTypeID)
> (select caseid, (Select NameID
> from tblname
> where longname =(@.casenumber+' '+ 'Debtor2')), '6'
> from tblcasedata
> where casenumber = @.casenumber)
>GO
>.
>

INSERT Records in multiple tables

I need to update two tables. I have created a view and am using the code in the attached file to insert into the two tables.

The page loads without errors, but I get this message that the view is not updatable because the modification affects multiple base tables.

I thought this was the purpose of views?

Does anyone have any suggestions? I am using Dreamweaver MX and SQL Server.

Thanks!
NNo, that is not the purpose of views. Views are frequently not updateable, and I don't think it is ever possible to update different columns from different tables in the same view. Even a direct SQL Update statement will only update one table at a time, so you will need to issues separate update statements or handle the problem through triggers or cascading updates.

Truth is, views don't serve much purpose any more.

Good database application design principles dictate making all your updates through stored procedures. Your application should rarely if ever have direct access to the database tables, even for retrieving data.|||No, you can update the columns of each of the base table independantly (one or more UPDATE statements per base table), but you can't update multiple base tables in a single pass.

Thinking outside of the SQL box, a table represents a relational algebra entity. An entity has no inherant order for either columns or rows, they behave something like a hash in that respect.

Views represent a relational algebra result. A result can have order, there can be a first, middle, and last for both rows and columns in a view.

-PatP|||Views (with multiple base tables) can be updated at one shot by using INSTEAD OF trigger

Here is some supporting article from MSDN

Cheers

Benny
-----------------------

Modifying Data Through a View
You can modify data through a view in these ways:

Use INSTEAD OF triggers with logic to support INSERT, UPDATE and DELETE statements.

Use updatable partitioned views that modify one or more member tables.
If a view does not use an INSTEAD OF trigger or is not an updatable partitioned view, it can still be updatable provided that:

The view contains at least one table in the FROM clause of the view definition; the view cannot be based solely on an expression.

No aggregate functions (AVG, COUNT, SUM, MIN, MAX, GROUPING, STDEV, STDEVP, VAR, VARP) or GROUP BY, UNION, DISTINCT, or TOP clauses are used in the select list. However, aggregate functions can be used within a subquery defined in the FROM clause provided that the derived values generated by the aggregate functions are not modified.

Note Partitioned views using the UNION ALL operator can be updatable.

No derived columns are used in the select list. Derived columns are result set columns formed by anything other than a simple column reference.
Guidelines for Modifying Data Through a View
Before you modify data through a view without using an INSTEAD OF trigger or an updatable partitioned view, consider these guidelines:

All data modification statements executed against the view must adhere to the criteria set within the SELECT statement defining the view if the WITH CHECK OPTION clause is used in the definition of the view. If the WITH CHECK OPTION clause is used, rows cannot be modified in a way that causes them to disappear from the view. Any modification that would cause this to happen is canceled and an error is displayed.

SQL Server must be able to resolve unambiguously the modification operation to specific rows in one of the base tables referenced by the view. You cannot use data modification statements on more than one underlying table in a single statement. Therefore, the columns listed in the UPDATE or INSERT statement must belong to a single base table within the view definition.

All the columns in the underlying table that are being updated and do not allow null values have values specified in either the INSERT statement or DEFAULT definitions. This ensures that all the columns in the underlying table that require values have them.

The data modified in the columns in the underlying table must adhere to the restrictions on those columns, such as nullability, constraints, DEFAULT definitions and so on. For example, if a row is deleted, all the underlying FOREIGN KEY constraints in related tables must still be satisfied for the delete to succeed.

A distributed partition view (remote view) cannot be updated using a keyset-driven cursor. This restriction can be resolved by declaring the cursor on the underlying tables and not on the view itself.
Additionally, to delete data in a view:

Only one table can be listed in the FROM clause of the view definition.|||Originally posted by blindman
Truth is, views don't serve much purpose any more.

what??!!

maybe not for use by the DBA, but for use by end users in a reporting environment, views are invaluable

"much purpose any more"?

what do you suppose the purpose of a view used to be then, before it got to where this purpose was diluted?

okay, here's an example

say a table is called Accounts and say it contains a column called LedgerCode and say the column values range from A to E, and now you have to change the table so that instead of values A to E, the LedgerCode becomes a numeric tinyint foreign key to a Ledger table with values A through Z

the mere fact that you can have a view with a join in it eliminates the need for the end user to figure out how to write a join

rename the table, change the table, declare a view called Accounts, build the join into the view, and voila, all existing code that used to select from the Accounts table still works

a long time ago i used to work in a shop where end users never got to use base tables, they were always given just views, and i can definitely see the logic behind that

it's called program-data independence|||Let me rephrase that...

Truth is, views don't serve much purpose any more, "IMHO".

I used to use views a lot too, specifically for program-data independence. Now, at least for application development, I always use Sprocs or UDFs.|||cool :cool:|||I believe you mean...

"Cool, IMHO." ;)|||indeed

burying application code inside sporcs and fuds is not cool to everybody, i admit -- especially those folks who would like to see a clear separation of application logic from proprietary database languages

usually i insist on declarative relational integrity but otherwise force application logic outside the database

you know, like so that your app is not dependent on any particular dbms

but sporcs and fuds are reasonably coolish, in my opinion, yeah|||Okey-dokey. I usually take the exact opposite approach, putting as much application logic into the RDBMS as possible, so that the application is not dependent on any particular interface. The reasoning is that these days people frequently want to access their data through different channels, such as a VB application, a Crystal Report, Access ADP project, Dot-Net, etc. By putting the application logic in the database you ensure consistent input and output and you avoid duplicating code. Let the the interface do what it does best: display the data and guide the user through it.

I guess the decision depends upon whether it is more likely that your application will need to be ported to a different RDBMS, or that users will come up with new requirements for accessing it. Perhaps I prefer the latter because the former results in boring "file cabinet" databases which frequently lack any sort of referential integrity. They just aren't as much fun or rewarding to work with as a database which is practically an application in itself.

Insert Parent Child records...

Hello,

We have a complex functionality of migrating data from a single record into multiple parent child tables.

To give you an example, lets us assume that we have a single table src_orders in the source database. We have a parent Order table and a child OrderDetails table in the target database. We need to pick one row from src_orders and insert this row in the Order table, pick up its PK (which is an identity column) and then use this to insert rows (say 5) in the OrderDetails table.

Again, we go back to the source, take a row, insert it into Orders, pick up the Orders PK and insert n rows in OrderDetails.

As of now, we are using the following approach for achieving this functionality.

1. Get the identity generated from the target table and store both the source table id and the target table id in a recordset.

2. Use the recordset as the source to a foreachloop , using foreachADO enumerator

3. Use data flow tasks to get the fields from the parent table for the source id, that needs to be inserted into the target child table

In case I have not ended up confusing everyone, can anyone validate this or suggest a better approach? :)

Thanks,

Satya

If it aint broke, don't fix it. There's no one correct way of acheiving something.

if it were me I would want to process all of the OrderDetails at the same time and it sounds like that's not what you're doing. Like I say though, if it works for you and performs in the required time, stick with it!

-Jamie

|||

As Jamie says, SSIS is a platform tool meaning that the infrastructure typically provides many ways to solve the same problem. However, here are a couple of alternatives that may work for you if you are having issues with the current approach.

1) Wrap this functionality in a stored procedure, and call the SP for every row. The SP would insert the parent item, retrieve the @.@.identity (actually, scope_identity()) and then insert the child. Perf may suck since you're calling the SP for every single row instead of bulk operations.

2) Wrap this functionality in a ScriptTransform that does pretty much the same as the previous bullet, only in VB.Net instead of TSQL. Once again, be careful of perf.

3) Wrap this functionality in a view that utilizes an InsteadOf trigger for the inserts. There are many contraints when going down this route however so your mileage may vary. For instance I cannot recall offhand if table joins are supported in this scenario; BOL has a wealth of info.