Inserting 1:M relationship data via One Stored Procedure

Hi,

Uses: SQL Server 2000, ASP.NET 1.1;

I've the following tables which has a 1:M relationship within them:

Contact(ContactID, LastName, FirstName, Address, Email, Fax)
ContactTelephone(ContactID, TelephoneNos)

I have a webform made with asp.net, and have given the user to add maximum of 3 telephone nos for a contact (Telephone Nos can be either Mobile or Land phones). So I've used Textbox's in the following way for the appropriate fields:

LastName,
FirstName,
Address,
Fax,
Email,
MobileNo,
PhoneNo1,
PhoneNo2,
PhoneNo3.

Once the submit button is pressed, I need to take all of this values and insert them in the tables via a Single Stored Procedure. I need to know could this be done and How?

Eagerly awaiting a response.

Thanks,

The best reference for this kind of thing when you truly have a 1:M relationship is Erland's web page: http://www.sommarskog.se/arrays-in-sql.html

But if you have a max of 3, then just write the proc with 3 parameters (something like):

create procedure contact$insert
(
@.LastName,
...
@.MobileNo,
@.PhoneNo1,
@.PhoneNo2,
@.PhoneNo3
)
--add your own error handling of course or add SET XACT_ABORT ON that
--will stop the tran on any error

begin tran

insert into contact (lastName, ..., MobileNo) --note, assuming contactId is an identity
values (@.lastName, ..., @.MobileNo)

declare @.newContactId int
set @.newContactId = scope_identity()

insert into contactTelephone
select @.newContactId, @.phoneNo1
where @.phoneNo1 is not null
union all
select @.newContactId, @.phoneNo2
where @.phoneNo2 is not null
union all
select @.newContactId, @.phoneNo3
where @.phoneNo3 is not null

commit tran

|||

Hi Louis,

Thanks for the Response, this cleared my mind and the problem. Thank you again!

sql

inserted text take the wrong alignment

i try to insert the following string in the database

the red car (driver)

this string save like this

)the red car (driver

i have a problem when inserting string contains special character at the end of the string.

we have arabic and english string like this

???? ????? (R) radial ????

and it appear in reverse like this

???? (R) radial ???? ?????

You need to check the application that is inserting the data specifically the API commands being used. This is not a SQL Server problem per se. The database engine will store the values as passed from the client and doesn't manipulate it on the server. Also, where are you checking the display of the values? It is possible that the tool is doing something based on your language / regional settings. So this could just be a display issue also. Start with verifying the data in the back end tables directly, then your client code and then whatever UI you are using.|||

hello Umachandar,

me and Batool posted this one together

I do import the data into the database through a certain script, but I thought it was an sql problem, because the data were in the correct alignment before inserting, I see them reversed in the tables directly, actually to test this issue I tried to enter data directly into the database so in the cell I press ctrl + Alt + shift to reverse the alignment inside the cell in table, and when I start submitting my data it is reversed.

how could this be a display problem when it's correct in all other applications on my machine

thank you

|||

I believe I've seen funny behavior in Management Studio when you try to display mixed right-left and left-right scripts. (I doubt this is unique to MS.) Can you inspect the binary contents of the strings and see whether it contains what you expect?

Cheers,

|||

You should verify the data first without involving any UI elements into the picture. The reason I say that it could be a display issue is that the tool might be doing something different when reading and displaying the data. This happens for float data type values today. The accuracy of the digits are different from ISQLW and in some cases two values that differ in say the 17th decimal digit will look the same. But this doesn't mean that the values are the same.

So you could write a script or program that does the insert, reads the data back and verifies it using SQL only. This will eliminate the UI from the picture. Additionally, tracing the calls to the server from the UI / tool via Profiler will also help. You can find out if the provider/driver is translating the string based on code page settings. There are just too many variables involved in this. Is it possible to do the following?

1. Post a simple DDL, insert statements and SELECT which shows the behavior (note that you may have to use the appropriate collation and Unicode data type to avoid any character translation)

2. If #1 doesn't work for you, is it possible to post some steps using say a particular UI (like ISQLW or SSMS). Please be clear on how you are inputting the data (open table, script/open table combination) and so on. Schema and data type of the column(s) are important here also. You talk about entering something in a cell - where is this? What UI are you talking about?

Lastly, the configuration of the OS (language/regional settings) may also be a factor and version of SQL Server. So please post those also.

inserted / deleted tables for triggers

Hi i was hoping someone could help me. If i have the following trigger
defined:
CREATE TRIGGER mytrigger ON mytableview
INSTEAD OF UPDATE
AS
UPDATE mytable SET
field1 = ISNULL(inserted.field1, 0),
field2 = ISNULL(inserted.field2, 0),
field3 = ISNULL(inserted.field3, 0)
FROM inserted
WHERE mytable.userid = inserted.userid
Iperform the following:
UPDATE mytableview
SET field1 = 1
WHERE userid = 1234
lets take for example the row pertaining to userid = 1234 within mytable to
be:
userid field1 field2 field3
1234 0 1 2
What is the state of the inserted table when the trigger is fired? Does the
inserted table do the following:
1) copy into itself the row from mytable pertaining to userid = 1234
2) modify this copied row to reflect field1 = 1
so inserted looks like this:
userid field1 field2 field3
1234 1 1 2
OR
1) creates a row within itself with field1 = 1, and all the other fields set
to NULL?
so inserted looks like this:
userid field1 field2 field3
NULL 1 NULL NULL
Ay help most appreciated. I think i am slightly with the state of
the inserted/deleted tables when triggers are invovled.
Cheers,
peterAn UPDATE with a trigger is performed as a DELETE followed by an INSERT. So
the DELETED table will contain the *before* data records and the INSERTED
table will contain the *after* data records.
HTH
Jerry
"PWalker" <pwalker@.nospam.com> wrote in message
news:OlU7ioz0FHA.2428@.tk2msftngp13.phx.gbl...
> Hi i was hoping someone could help me. If i have the following trigger
> defined:
> CREATE TRIGGER mytrigger ON mytableview
> INSTEAD OF UPDATE
> AS
> UPDATE mytable SET
> field1 = ISNULL(inserted.field1, 0),
> field2 = ISNULL(inserted.field2, 0),
> field3 = ISNULL(inserted.field3, 0)
> FROM inserted
> WHERE mytable.userid = inserted.userid
>
> Iperform the following:
>
> UPDATE mytableview
> SET field1 = 1
> WHERE userid = 1234
>
> lets take for example the row pertaining to userid = 1234 within mytable
> to be:
> userid field1 field2 field3
> 1234 0 1 2
> --
> What is the state of the inserted table when the trigger is fired? Does
> the inserted table do the following:
> 1) copy into itself the row from mytable pertaining to userid = 1234
> 2) modify this copied row to reflect field1 = 1
> so inserted looks like this:
> userid field1 field2 field3
> 1234 1 1 2
> OR
> 1) creates a row within itself with field1 = 1, and all the other fields
> set to NULL?
> so inserted looks like this:
> userid field1 field2 field3
> NULL 1 NULL NULL
>
> Ay help most appreciated. I think i am slightly with the state of
> the inserted/deleted tables when triggers are invovled.
> Cheers,
> peter
>|||thanks, so an update removes the relevant row(s) from the trigger table and
sticks them into the deleted table; then inserts the new modified row(s)
into both the trigger table and the inserted table.
thanks for the clarification.
cheers, peter

> An UPDATE with a trigger is performed as a DELETE followed by an INSERT.
> So the DELETED table will contain the *before* data records and the
> INSERTED table will contain the *after* data records.
> HTH
> Jerry
> "PWalker" <pwalker@.nospam.com> wrote in message
> news:OlU7ioz0FHA.2428@.tk2msftngp13.phx.gbl...
>

Inserted & Deleted Tables!

Suppose a trigger gets fired when the following UPDATE query gets
executed:
---
UPDATE Users SET Pwd='12345' WHERE UserID='jack' AND Pwd='11111'
---
Now the Inserted table will have the new record '12345' in the Pwd
column & the Deleted table will have the old record '11111' in the Pwd
column. So will the record 'jack' exist in the UserID column of both
the Inserted table & the Deleted table that the trigger will be making
use of?
Thanks,
ArpanHi
Yes, the whole row, as it was before and after are in the respective tables,
not just the column that changed.
If you update the primary key of a table, then comparing the Inserted and
Deleted becomes very difficult.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Arpan" <arpan_de@.hotmail.com> wrote in message
news:1123800474.961292.259990@.g49g2000cwa.googlegroups.com...
> Suppose a trigger gets fired when the following UPDATE query gets
> executed:
> ---
> UPDATE Users SET Pwd='12345' WHERE UserID='jack' AND Pwd='11111'
> ---
> Now the Inserted table will have the new record '12345' in the Pwd
> column & the Deleted table will have the old record '11111' in the Pwd
> column. So will the record 'jack' exist in the UserID column of both
> the Inserted table & the Deleted table that the trigger will be making
> use of?
> Thanks,
> Arpan
>|||On 11 Aug 2005 15:47:55 -0700, Arpan wrote:

>Suppose a trigger gets fired when the following UPDATE query gets
>executed:
>---
>UPDATE Users SET Pwd='12345' WHERE UserID='jack' AND Pwd='11111'
>---
>Now the Inserted table will have the new record '12345' in the Pwd
>column & the Deleted table will have the old record '11111' in the Pwd
>column. So will the record 'jack' exist in the UserID column of both
>the Inserted table & the Deleted table that the trigger will be making
>use of?
Hi Arpan,
Almost.
The exact correct way to put this is:
- The deleted table will hold 0, 1, or many rows that all have UserID
'jack' and Pwd '11111'. Impossible to tell what the other columns will
be.
- The inserted table will hold 0, 1, or many rows (but the same number
as the deleted table) that all have UserID 'jack' and Pwd '12345'; the
other columns will be the same as in the corresponding rows in the
deleted table.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Insert/Update into a SQL table

I have the following keys in Consumption:
- Plant
- Material
- Month
- Year
The above are the primary keys in the table and the following are
non-key fields:
- Quantity
- Amount
I have data stored in this table currently but many times I get feeds
which are stored in the table:
Consumption_staging which as the following fields:
Plant
Material
Month
Year
Quantity
Amount
even in the staging table - plant, material, month,year are the keys.
Now I want to update data from the Consumption_staging to the
Consumption table on the following criteria:
If for the same Key fields as in Consumption_Staging if a record is
already present in Consumption table then the record in Consumption
must be updated with the non-key fields else the record from
Consumption_staging must be inserted into the Consumption table.
Greatly appreciate if you could kindly share the SQL code for this
problem I want to just do it possibly just in SQL.
Thanks
Karen
update Consumption
set p = CS.p , ma = CS.ma , mo = CS.mo , yr = CS.yr
from Consumption_staging CS
inner join Consumption C
ON CS.p = C.p AND CS.ma = C.ma AND CS.mo = C.mo AND CS.yr = C.yr
insert into Consumption ( p , ma, mo, yr, qu, am )
Select p , ma, mo, yr, qu, am from Consumption_staging CS
left outer join Consumption C
ON CS.p = C.p AND CS.ma = C.ma AND CS.mo = C.mo AND CS.yr = C.yr
WHERE C.p IS NULL
<karenmiddleol@.yahoo.com> wrote in message
news:1129633654.148651.61570@.g14g2000cwa.googlegro ups.com...
> I have the following keys in Consumption:
> - Plant
> - Material
> - Month
> - Year
> The above are the primary keys in the table and the following are
> non-key fields:
> - Quantity
> - Amount
> I have data stored in this table currently but many times I get feeds
> which are stored in the table:
> Consumption_staging which as the following fields:
> Plant
> Material
> Month
> Year
> Quantity
> Amount
> even in the staging table - plant, material, month,year are the keys.
> Now I want to update data from the Consumption_staging to the
> Consumption table on the following criteria:
> If for the same Key fields as in Consumption_Staging if a record is
> already present in Consumption table then the record in Consumption
> must be updated with the non-key fields else the record from
> Consumption_staging must be inserted into the Consumption table.
> Greatly appreciate if you could kindly share the SQL code for this
> problem I want to just do it possibly just in SQL.
> Thanks
> Karen
>
|||Ooops, I booboo'd on the update
update Consumption
set qu = CS.qu , am = CS.am
from Consumption_staging CS
inner join Consumption C
ON CS.p = C.p AND CS.ma = C.ma AND CS.mo = C.mo AND CS.yr = C.yr
"Rebecca York" <rebecca.york {at} 2ndbyte.com> wrote in message
news:4354d16b$0$134$7b0f0fd3@.mistral.news.newnet.c o.uk...
> update Consumption
> set p = CS.p , ma = CS.ma , mo = CS.mo , yr = CS.yr
> from Consumption_staging CS
> inner join Consumption C
> ON CS.p = C.p AND CS.ma = C.ma AND CS.mo = C.mo AND CS.yr = C.yr
> insert into Consumption ( p , ma, mo, yr, qu, am )
> Select p , ma, mo, yr, qu, am from Consumption_staging CS
> left outer join Consumption C
> ON CS.p = C.p AND CS.ma = C.ma AND CS.mo = C.mo AND CS.yr = C.yr
> WHERE C.p IS NULL
>
>
> <karenmiddleol@.yahoo.com> wrote in message
> news:1129633654.148651.61570@.g14g2000cwa.googlegro ups.com...
>
|||Many thanks the update query works fine but the Insert comes back with
this error:
Server: Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'Plant', table
'TestDB.dbo.Consumption'; column does not allow nulls. INSERT fails.
The statement has been terminated.
Plant is part of the Primary key and the system obviously does not
allow nulls. But in the staging table there is no null value in the
Plant field.
But the insert never works please appreciate
Thanks
Karen
|||The insert gives more errors as follows:
Server: Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK_Consumption. Cannot insert
duplicate key in object 'Consumption'.
The statement has been terminated.
Thanks
Karen
|||It sounds like
1. Your source data has missing data
2. Your source data has duplicate data.
Best solution is to ask whoever sent you the file to fix the data export.
Try this to give you the duplicated records
SELECT p , ma , mo , yr FROM Consumption_staging GROUP BY p , ma , mo , yr
HAVING COUNT(*) > 1
This will give you records with missing data.
SET CONCAT_NULL_YIELDS_NULL ON
SELECT p , ma , mo , yr FROM Consumption_staging
WHERE p IS NULL or ma IS NULL or mo IS NULL or yr IS NULL
HTH
<karenmiddleol@.yahoo.com> wrote in message
news:1129641278.899750.254090@.z14g2000cwz.googlegr oups.com...
> The insert gives more errors as follows:
> Server: Msg 2627, Level 14, State 1, Line 1
> Violation of PRIMARY KEY constraint 'PK_Consumption. Cannot insert
> duplicate key in object 'Consumption'.
> The statement has been terminated.
> Thanks
> Karen
>

Insert/Update into a SQL table

I have the following keys in Consumption:
- Plant
- Material
- Month
- Year
The above are the primary keys in the table and the following are
non-key fields:
- Quantity
- Amount
I have data stored in this table currently but many times I get feeds
which are stored in the table:
Consumption_staging which as the following fields:
Plant
Material
Month
Year
Quantity
Amount
even in the staging table - plant, material, month,year are the keys.
Now I want to update data from the Consumption_staging to the
Consumption table on the following criteria:
If for the same Key fields as in Consumption_Staging if a record is
already present in Consumption table then the record in Consumption
must be updated with the non-key fields else the record from
Consumption_staging must be inserted into the Consumption table.
Greatly appreciate if you could kindly share the SQL code for this
problem I want to just do it possibly just in SQL.
Thanks
Karenupdate Consumption
set p = CS.p , ma = CS.ma , mo = CS.mo , yr = CS.yr
from Consumption_staging CS
inner join Consumption C
ON CS.p = C.p AND CS.ma = C.ma AND CS.mo = C.mo AND CS.yr = C.yr
insert into Consumption ( p , ma, mo, yr, qu, am )
Select p , ma, mo, yr, qu, am from Consumption_staging CS
left outer join Consumption C
ON CS.p = C.p AND CS.ma = C.ma AND CS.mo = C.mo AND CS.yr = C.yr
WHERE C.p IS NULL
<karenmiddleol@.yahoo.com> wrote in message
news:1129633654.148651.61570@.g14g2000cwa.googlegroups.com...
> I have the following keys in Consumption:
> - Plant
> - Material
> - Month
> - Year
> The above are the primary keys in the table and the following are
> non-key fields:
> - Quantity
> - Amount
> I have data stored in this table currently but many times I get feeds
> which are stored in the table:
> Consumption_staging which as the following fields:
> Plant
> Material
> Month
> Year
> Quantity
> Amount
> even in the staging table - plant, material, month,year are the keys.
> Now I want to update data from the Consumption_staging to the
> Consumption table on the following criteria:
> If for the same Key fields as in Consumption_Staging if a record is
> already present in Consumption table then the record in Consumption
> must be updated with the non-key fields else the record from
> Consumption_staging must be inserted into the Consumption table.
> Greatly appreciate if you could kindly share the SQL code for this
> problem I want to just do it possibly just in SQL.
> Thanks
> Karen
>|||Ooops, I booboo'd on the update :)
update Consumption
set qu = CS.qu , am = CS.am
from Consumption_staging CS
inner join Consumption C
ON CS.p = C.p AND CS.ma = C.ma AND CS.mo = C.mo AND CS.yr = C.yr
"Rebecca York" <rebecca.york {at} 2ndbyte.com> wrote in message
news:4354d16b$0$134$7b0f0fd3@.mistral.news.newnet.co.uk...
> update Consumption
> set p = CS.p , ma = CS.ma , mo = CS.mo , yr = CS.yr
> from Consumption_staging CS
> inner join Consumption C
> ON CS.p = C.p AND CS.ma = C.ma AND CS.mo = C.mo AND CS.yr = C.yr
> insert into Consumption ( p , ma, mo, yr, qu, am )
> Select p , ma, mo, yr, qu, am from Consumption_staging CS
> left outer join Consumption C
> ON CS.p = C.p AND CS.ma = C.ma AND CS.mo = C.mo AND CS.yr = C.yr
> WHERE C.p IS NULL
>
>
> <karenmiddleol@.yahoo.com> wrote in message
> news:1129633654.148651.61570@.g14g2000cwa.googlegroups.com...
>|||Many thanks the update query works fine but the Insert comes back with
this error:
Server: Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'Plant', table
'TestDB.dbo.Consumption'; column does not allow nulls. INSERT fails.
The statement has been terminated.
Plant is part of the Primary key and the system obviously does not
allow nulls. But in the staging table there is no null value in the
Plant field.
But the insert never works please appreciate
Thanks
Karen|||The insert gives more errors as follows:
Server: Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK_Consumption. Cannot insert
duplicate key in object 'Consumption'.
The statement has been terminated.
Thanks
Karen|||It sounds like
1. Your source data has missing data
2. Your source data has duplicate data.
Best solution is to ask whoever sent you the file to fix the data export.
Try this to give you the duplicated records
SELECT p , ma , mo , yr FROM Consumption_staging GROUP BY p , ma , mo , yr
HAVING COUNT(*) > 1
This will give you records with missing data.
SET CONCAT_NULL_YIELDS_NULL ON
SELECT p , ma , mo , yr FROM Consumption_staging
WHERE p IS NULL or ma IS NULL or mo IS NULL or yr IS NULL
HTH
<karenmiddleol@.yahoo.com> wrote in message
news:1129641278.899750.254090@.z14g2000cwz.googlegroups.com...
> The insert gives more errors as follows:
> Server: Msg 2627, Level 14, State 1, Line 1
> Violation of PRIMARY KEY constraint 'PK_Consumption. Cannot insert
> duplicate key in object 'Consumption'.
> The statement has been terminated.
> Thanks
> Karen
>

Insert/Update into a SQL table

I have the following keys in Consumption:
- Plant
- Material
- Month
- Year
The above are the primary keys in the table and the following are
non-key fields:
- Quantity
- Amount
I have data stored in this table currently but many times I get feeds
which are stored in the table:
Consumption_staging which as the following fields:
Plant
Material
Month
Year
Quantity
Amount
even in the staging table - plant, material, month,year are the keys.
Now I want to update data from the Consumption_staging to the
Consumption table on the following criteria:
If for the same Key fields as in Consumption_Staging if a record is
already present in Consumption table then the record in Consumption
must be updated with the non-key fields else the record from
Consumption_staging must be inserted into the Consumption table.
Greatly appreciate if you could kindly share the SQL code for this
problem I want to just do it possibly just in SQL.
Thanks
Karenupdate Consumption
set p = CS.p , ma = CS.ma , mo = CS.mo , yr = CS.yr
from Consumption_staging CS
inner join Consumption C
ON CS.p = C.p AND CS.ma = C.ma AND CS.mo = C.mo AND CS.yr = C.yr
insert into Consumption ( p , ma, mo, yr, qu, am )
Select p , ma, mo, yr, qu, am from Consumption_staging CS
left outer join Consumption C
ON CS.p = C.p AND CS.ma = C.ma AND CS.mo = C.mo AND CS.yr = C.yr
WHERE C.p IS NULL
<karenmiddleol@.yahoo.com> wrote in message
news:1129633654.148651.61570@.g14g2000cwa.googlegroups.com...
> I have the following keys in Consumption:
> - Plant
> - Material
> - Month
> - Year
> The above are the primary keys in the table and the following are
> non-key fields:
> - Quantity
> - Amount
> I have data stored in this table currently but many times I get feeds
> which are stored in the table:
> Consumption_staging which as the following fields:
> Plant
> Material
> Month
> Year
> Quantity
> Amount
> even in the staging table - plant, material, month,year are the keys.
> Now I want to update data from the Consumption_staging to the
> Consumption table on the following criteria:
> If for the same Key fields as in Consumption_Staging if a record is
> already present in Consumption table then the record in Consumption
> must be updated with the non-key fields else the record from
> Consumption_staging must be inserted into the Consumption table.
> Greatly appreciate if you could kindly share the SQL code for this
> problem I want to just do it possibly just in SQL.
> Thanks
> Karen
>|||Ooops, I booboo'd on the update :)
update Consumption
set qu = CS.qu , am = CS.am
from Consumption_staging CS
inner join Consumption C
ON CS.p = C.p AND CS.ma = C.ma AND CS.mo = C.mo AND CS.yr = C.yr
"Rebecca York" <rebecca.york {at} 2ndbyte.com> wrote in message
news:4354d16b$0$134$7b0f0fd3@.mistral.news.newnet.co.uk...
> update Consumption
> set p = CS.p , ma = CS.ma , mo = CS.mo , yr = CS.yr
> from Consumption_staging CS
> inner join Consumption C
> ON CS.p = C.p AND CS.ma = C.ma AND CS.mo = C.mo AND CS.yr = C.yr
> insert into Consumption ( p , ma, mo, yr, qu, am )
> Select p , ma, mo, yr, qu, am from Consumption_staging CS
> left outer join Consumption C
> ON CS.p = C.p AND CS.ma = C.ma AND CS.mo = C.mo AND CS.yr = C.yr
> WHERE C.p IS NULL
>
>
> <karenmiddleol@.yahoo.com> wrote in message
> news:1129633654.148651.61570@.g14g2000cwa.googlegroups.com...
> > I have the following keys in Consumption:
> >
> > - Plant
> > - Material
> > - Month
> > - Year
> >
> > The above are the primary keys in the table and the following are
> > non-key fields:
> >
> > - Quantity
> > - Amount
> >
> > I have data stored in this table currently but many times I get feeds
> > which are stored in the table:
> >
> > Consumption_staging which as the following fields:
> >
> > Plant
> > Material
> > Month
> > Year
> > Quantity
> > Amount
> >
> > even in the staging table - plant, material, month,year are the keys.
> >
> > Now I want to update data from the Consumption_staging to the
> > Consumption table on the following criteria:
> >
> > If for the same Key fields as in Consumption_Staging if a record is
> > already present in Consumption table then the record in Consumption
> > must be updated with the non-key fields else the record from
> > Consumption_staging must be inserted into the Consumption table.
> >
> > Greatly appreciate if you could kindly share the SQL code for this
> > problem I want to just do it possibly just in SQL.
> >
> > Thanks
> > Karen
> >
>|||Many thanks the update query works fine but the Insert comes back with
this error:
Server: Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'Plant', table
'TestDB.dbo.Consumption'; column does not allow nulls. INSERT fails.
The statement has been terminated.
Plant is part of the Primary key and the system obviously does not
allow nulls. But in the staging table there is no null value in the
Plant field.
But the insert never works please appreciate
Thanks
Karen|||The insert gives more errors as follows:
Server: Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK_Consumption. Cannot insert
duplicate key in object 'Consumption'.
The statement has been terminated.
Thanks
Karen|||It sounds like
1. Your source data has missing data
2. Your source data has duplicate data.
Best solution is to ask whoever sent you the file to fix the data export.
Try this to give you the duplicated records
SELECT p , ma , mo , yr FROM Consumption_staging GROUP BY p , ma , mo , yr
HAVING COUNT(*) > 1
This will give you records with missing data.
SET CONCAT_NULL_YIELDS_NULL ON
SELECT p , ma , mo , yr FROM Consumption_staging
WHERE p IS NULL or ma IS NULL or mo IS NULL or yr IS NULL
HTH
<karenmiddleol@.yahoo.com> wrote in message
news:1129641278.899750.254090@.z14g2000cwz.googlegroups.com...
> The insert gives more errors as follows:
> Server: Msg 2627, Level 14, State 1, Line 1
> Violation of PRIMARY KEY constraint 'PK_Consumption. Cannot insert
> duplicate key in object 'Consumption'.
> The statement has been terminated.
> Thanks
> Karen
>

insert/select

I am trying to find an easier way to handle my insert/select statement. If
I have the following tables and query - Is there a way to do this in one
statement?
create table Master(
MasterKey int,
)
Create table SubTable1(
MasterKey int,
PK int,
Data varChar(1000)
)
Create table SubTable2(
MasterKey int,
PK int,
Data varChar(1000
)
Master Table
MasterKey Priority
1 0
2 0
3 1
4 0
5 0
6 0
SubTable1
Empty
SubTable2
MasterKey PK
1 1
1 1
1 2
2 3
2 3
3 4
3 4
3 4
4 4
4 4
4 5
4 5
What I want to be able to do is move data from SubTable2 to SubTable1
I tried to do something like:
Select @.MasterKey from Master where Priority = 1 (this would give me
a MasterKey of 3)
insert (MasterKey,PK,Data)
Select @.MasterKey,PK,Data
From SubTable2
Where PK = 4
This would move/create 5 records with a @.MasterKey of 3 into the SubTable1.
This works as long as there is only
one MasterKey. But what if I want to create a 5 records for all (or a
potion) of the MasterKeys.
I could reexecute the command multiple times from a loop to get the results
I want, but I was curious if there was an easier way, using one SQL
Statement.
Thanks,
Tomtshad, it is not clear what you are trying to do. In the example you give
you would end up with 5 records in SubTable1 that all had MasterKey = 3 and
PK = 4, that doesn't seem to make much sense.
If you explain it better I can probably help. I think you may want to use an
IN list in the SELECT, so something like:
insert (MasterKey,PK,Data)
Select @.MasterKey,PK,Data
From SubTable2
Where PK IN (4, 5, 6)
or otherwise you may need to use a subquery or a join, but I just can't tell
what you're trying to do.
Sean
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:uIw5J5tjFHA.3756@.TK2MSFTNGP15.phx.gbl...
>I am trying to find an easier way to handle my insert/select statement. If
>I have the following tables and query - Is there a way to do this in one
>statement?
> create table Master(
> MasterKey int,
> )
> Create table SubTable1(
> MasterKey int,
> PK int,
> Data varChar(1000)
> )
>
> Create table SubTable2(
> MasterKey int,
> PK int,
> Data varChar(1000
> )
> Master Table
> MasterKey Priority
> 1 0
> 2 0
> 3 1
> 4 0
> 5 0
> 6 0
> SubTable1
> Empty
> SubTable2
> MasterKey PK
> 1 1
> 1 1
> 1 2
> 2 3
> 2 3
> 3 4
> 3 4
> 3 4
> 4 4
> 4 4
> 4 5
> 4 5
> What I want to be able to do is move data from SubTable2 to SubTable1
> I tried to do something like:
> Select @.MasterKey from Master where Priority = 1 (this would give
> me a MasterKey of 3)
> insert (MasterKey,PK,Data)
> Select @.MasterKey,PK,Data
> From SubTable2
> Where PK = 4
> This would move/create 5 records with a @.MasterKey of 3 into the
> SubTable1. This works as long as there is only
> one MasterKey. But what if I want to create a 5 records for all (or a
> potion) of the MasterKeys.
> I could reexecute the command multiple times from a loop to get the
> results I want, but I was curious if there was an easier way, using one
> SQL Statement.
> Thanks,
> Tom
>
>

Insert, Update & Delete on two tables with same data structure...

I have created two table with same data structure. I need realtime effects (i.e. data) on both tables - Table1 & Table2.

Following Points to Consider.

1. Both tables are in the same database.

2. Table1 is using for data entry & I wants the same data in the Table2.

3. If any row insert, update & delete occers on Table1, the same effect should be done on Table2.

4. I need real time data insert, update & delete on Table2.

I knew that using triggers it could be possible, I have successfully created a trigger for inserting new rows (using logical table "Inserted") in Table2 but not succeed for update & delete yet.

I want to understand how can I impletement this successfully without any ambiguity.

I have attached data structure for tables. Thanx...You want TWO tables with IDENTICAL structure and the SAME data in a SINGLE database?
We can help you debug your triggers if you post the code, but WHY?|||Actually we required some reports and as we have old version application, it could not be possible to generate required reports.

The data is dynamic (i.e. Table1) & changing with the stock quantity IN & OUT, thats why I will store data for specific span of time in the new table (Table2). I will use that data for reporting.

Which code you required..? I have attached script for creating a tables.|||I understand you want inserts copied to the second table.
What about updates? Do you want the data in the second table updated, or do you want a new record added instead?
What about deletes? Do you want the data in the second table deleted as well, or do you just want to mark the record as deleted?

Did you try writing triggers for Update and Delete? If so, post the code for those triggers and we will help you debug it our fix syntax errors.|||oye...redundant data...

In any case if you follow the Hint link sticky at the top of the forum and post what it tells you, I'm sure we can supply enough rope|||As I have told that the data entry done through frontend, I want each and every effect (i.e. row insert, update or delete) on Table2.

As I have told you that the second table I am using for reporting purpose & the reports will be wrong if it is not reflect the data which entered or modified last.

May be you think its too cumbersome but now let me explain the full scenario.

1. I have to do this because I have added new column in the Table2 which is not part of Table1.

2. Using insert trigger on Table1 I can add new row in Table2 same as Table1 as well as I can feed data in the new added column which is not part of Table1.

3. Whenever row inserted, update or delete in Table1 the Table2 should update accordingly.

4. I can not cascade update or delete because both tables are having only foreign keys. (cFinYrs, cLocCode, cMonth, cItemCode are foreign keys)

5. I will re-write triggers according to my requirement, but I need little help to be clear of the concept from you expert guys.

6. The script which I have given for creating a tables will create the same data structure for tables.

I have written trigger for insert, it's given below. It's working good for insert.

CREATE TRIGGER [InForRpt] ON [dbo].[Table1]
FOR INSERT

AS

Declare @.cFinYrs varchar(3)
Declare @.cLocCode varchar(7)
Declare @.cMonth varchar(3)
Declare @.iSrNo int
Declare @.cItemCode varchar(20)
Declare @.dQty dec
Declare @.dRate dec(9,2)
Declare @.cDesc varchar(200)
Declare @.cCreated varchar(6)
Declare @.dtcreated datetime
Declare @.cModified varchar(6)
Declare @.dtModified datetime
Declare @.cMachIP varchar(15)

SET @.cFinYrs = (select cFinYrs from inserted)
SET @.cLocCode = (select cLocCode from inserted)
SET @.cMonth = (select cMonth from inserted)
SET @.iSrNo = (select iSrNo from inserted)
SET @.cItemCode = (select cItemCode from inserted)
SET @.dQty = (select dQty from inserted)
SET @.cDesc = (select cDesc from inserted)
SET @.cCreated = (select cCreated from inserted)
SET @.dtcreated = (select dtCreated from inserted)
SET @.cModified = (select cModified from inserted)
SET @.dtModified = (select dtModified from inserted)
SET @.cMachIP = (select cMachIP from inserted)

Select @.dRate = drate from ssstockmst where citemcode=@.cItemCode

Insert INTO Table2 values(@.cFinYrs, @.cLocCode, @.cMonth,
@.iSrNo, @.cItemCode, @.dQty,
@.dRate, @.cDesc, @.cCreated,
@.dtCreated, @.cModified,
@.dtModified, @.cMachIP)

How I can make this happen..? Thanx for replying...|||oye...redundant data...

Yeah it could be redundant data but it will helps me lot to produce reports according to management requirement. And this data will not be heavy in size (1 to 5MB) so don't affect the server space as we have provision for same. :)|||I have to do this because I have added new column in the Table2 which is not part of Table1.This still makes no sense. Why not just add the column to Table1? Are you dealing with a reduced record set in table2? Is that data truncated occasionally, or filtered? We need to know how that data is being retained before helping you create Update/Delete triggers.

But regarding your insert trigger...

The method you have chosen is not only slow and verbose, but will also fail if more than one record is inserted into the table by a single transaction. Triggers MUST be designed to function correctly with multi-record inserts.

No exceptions.

This is the method you want to use for your insert trigger:CREATE TRIGGER [InForRpt] ON [dbo].[Table1]
FOR INSERT

AS
begin
insert into Table2
(cFinYrs,
cLocCode,
cMonth,
iSrNo,
cItemCode,
dQty,
dRate,
cDesc,
cCreated,
dtCreated,
cModified,
dtModified,
cMachIP)
select inserted.cFinYrs,
inserted.cLocCode,
inserted.cMonth,
inserted.iSrNo,
inserted.cItemCode,
inserted.dQty,
ssstockmst.dRate,
inserted.cDesc,
inserted.cCreated,
inserted.dtCreated,
inserted.cModified,
inserted.dtModified,
inserted.cMachIP
from inserted
left outer join ssstockmst on inserted.cItemCode = ssstockmst.cItemCode
endMuch simpler, eh?
Now, I really recommend that you go back to Books Online and read the sections on triggers, paying careful attention to the examples given.|||This still makes no sense. Why not just add the column to Table1? Are you dealing with a reduced record set in table2? Is that data truncated occasionally, or filtered? We need to know how that data is being retained before helping you create Update/Delete triggers.

I thought to add new column in the Table1 but Table1 is being used & lots of data in the table1. Second thing, I have to think of the forntend application too.

We found best solution for a while is to create a new table for same & we will update the table1 later, when we upgrade our database & application.

No, data is truncated...

Thanx blindman, for simplify insert trigger...|||I thought to add new column in the Table1 but Table1 is being used & lots of data in the table1. Second thing, I have to think of the forntend application too.Still makes no sense. You're taking up extra space by storing redundant data in table2, extra processing time by keeping the data synchronized, extra development time in setting up this process, and a properly designed front-end won't care or even know that you've added an extra column to the table.
You need a DBA to help you with this project...|||I have solved the problem.

1. I have created a surrogate key (combination of cFinYrs, cLocCode, cMonth & cItemCode) on Table1 and accordingly foreign key on Table2.

2. Set cascade for update & delete.

I have test it, it's working fine.

I am understanding what you want to say but right now I am not allowed to modify working table's structure. Surely I will do it but later.

Thanx blindman for all efforts you placed...

Insert, Calculations & Where

Hi

I sometimes find myself in the situation where I want to insert a row into a table using the following form:
insert table ( <field list> ) select <field list> from .. etc .. Where <conditions>

My question is to do with where one or more of the fields in the select field list are calculations and where I also want to use some/all of these derived fields as Where conditions. [ Eg: only insert if the calculated value is > 0]

I currently either repeat the calculation in the Where clause or move it to a function and use the function call in both places. (I always get a pang of guilt using either option - repeating the calculation feels like bad practice - & using the function twice seems inefficient (does this get optimised?)).

I could get a life & stop worrying - but is there a better/neater way of doing this?

Many thanks.An exact DML sample would be helpful here...but if you need to INSERT a derived field, and need to make sure that the derived field is > 0 for example, then you have no choice...|||Use HAVING clause|||Use HAVING clause

Is it 5:00 already in texas?

Insert using a case condition

Hello, SQL Gurus, I am trying to use the case statement in the following
logic and just can't seem to get it to work. Any help would be greatly
appreciated. Thanks a bunch.
INSERT INTO dbo.match_table
SELECT
a.[Glsubact], a.[Dcoscat], [a.Revreduc],a.[Custtype], b.[Tier2] , b.
New_entry, a.PRODTYPE
from BAL_table a JOIN Tier2_table b
ON
b.Custtype = a.Custtype
and b. ICPROFCT = a.ICPROFCT
and ( substring(b.Profctr,1,2)+ '00' ) = a.Profctr
CASE WHEN(PRODTYPE not in ('01','04') and New_entry = 'N') Then
set TIER2 = 'C092'
Else
set TIER2 = 'C094'
EndCould you shed some light on what you are trying to accomplish with the CASE
expression? I can't tell from your post code. Is it supposed to be part of
the SELECT statement (perhaps in its where clause) or part of the join
condition?
Note that CASE ... END is an expression in T-SQL, not a statement. As an
expression, you can't enclose a T-SQL statement in it.
Linchi
"mbouck" wrote:
> Hello, SQL Gurus, I am trying to use the case statement in the following
> logic and just can't seem to get it to work. Any help would be greatly
> appreciated. Thanks a bunch.
>
> INSERT INTO dbo.match_table
> SELECT
> a.[Glsubact], a.[Dcoscat], [a.Revreduc],a.[Custtype], b.[Tier2] , b.
> New_entry, a.PRODTYPE
> from BAL_table a JOIN Tier2_table b
> ON
> b.Custtype = a.Custtype
> and b. ICPROFCT = a.ICPROFCT
> and ( substring(b.Profctr,1,2)+ '00' ) = a.Profctr
> CASE WHEN(PRODTYPE not in ('01','04') and New_entry = 'N') Then
> set TIER2 = 'C092'
> Else
> set TIER2 = 'C094'
> End
>|||Hi
Some ideas
http://dimantdatabasesolutions.blogspot.com/2007/02/some-case-expression-techniques.html
"mbouck" <u32265@.uwe> wrote in message news:6ec0823937b99@.uwe...
> Hello, SQL Gurus, I am trying to use the case statement in the following
> logic and just can't seem to get it to work. Any help would be greatly
> appreciated. Thanks a bunch.
>
> INSERT INTO dbo.match_table
> SELECT
> a.[Glsubact], a.[Dcoscat], [a.Revreduc],a.[Custtype], b.[Tier2] , b.
> New_entry, a.PRODTYPE
> from BAL_table a JOIN Tier2_table b
> ON
> b.Custtype = a.Custtype
> and b. ICPROFCT = a.ICPROFCT
> and ( substring(b.Profctr,1,2)+ '00' ) = a.Profctr
> CASE WHEN(PRODTYPE not in ('01','04') and New_entry = 'N') Then
> set TIER2 = 'C092'
> Else
> set TIER2 = 'C094'
> End
>|||Hi Uri, the case examples that you have posted are very helpful, I will try
them. thanks for the help.
Uri Dimant wrote:
>Hi
>Some ideas
>http://dimantdatabasesolutions.blogspot.com/2007/02/some-case-expression-techniques.html
>> Hello, SQL Gurus, I am trying to use the case statement in the following
>> logic and just can't seem to get it to work. Any help would be greatly
>[quoted text clipped - 20 lines]
>> End
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200703/1|||Hi Linchi, thank you for responding back and the guidance. I'll try rewriting
it again.
Linchi Shea wrote:
>Could you shed some light on what you are trying to accomplish with the CASE
>expression? I can't tell from your post code. Is it supposed to be part of
>the SELECT statement (perhaps in its where clause) or part of the join
>condition?
>Note that CASE ... END is an expression in T-SQL, not a statement. As an
>expression, you can't enclose a T-SQL statement in it.
>Linchi
>> Hello, SQL Gurus, I am trying to use the case statement in the following
>> logic and just can't seem to get it to work. Any help would be greatly
>[quoted text clipped - 20 lines]
>> End
--
Message posted via http://www.sqlmonster.com

Insert Trigger Help

Hello,

I'm new with triggers and I can not find any good example on how to
do the following:

I have two tables WO and PM with the following fields:

WO.WONUM, VARCHAR(10)
WO.PMNUM, VARCHAR(10)
WO.PROBLEMCODE, VARCHAR(8)
WO.LABORGROUP, VARCHAR(8)

PM.PMNUM, VARCHAR(10)
PM.PROBLEMCODE, VARCHAR(8)
PM.LABORGROUP, VARCHAR(8)

When creating a new record on WO I need to create an INSERT TRIGGER
that will pass the data below from PM to WO when WO.PMNUM = PM.PMNUM

PM.PROBLEMCODE to WO. PROBLEMCODE and
PM.LABORGROUP to WO. LABORGROUP

Could anybody please show me how to do this or point me to the right
direction, any help will be greatly appreciated.

Thanks!

Martin"Martin" <martin.wunder@.wsidc.com> wrote in message
news:1104861858.065373.23600@.c13g2000cwb.googlegro ups.com...
> Hello,
> I'm new with triggers and I can not find any good example on how to
> do the following:
> I have two tables WO and PM with the following fields:
> WO.WONUM, VARCHAR(10)
> WO.PMNUM, VARCHAR(10)
> WO.PROBLEMCODE, VARCHAR(8)
> WO.LABORGROUP, VARCHAR(8)
> PM.PMNUM, VARCHAR(10)
> PM.PROBLEMCODE, VARCHAR(8)
> PM.LABORGROUP, VARCHAR(8)
> When creating a new record on WO I need to create an INSERT TRIGGER
> that will pass the data below from PM to WO when WO.PMNUM = PM.PMNUM
> PM.PROBLEMCODE to WO. PROBLEMCODE and
> PM.LABORGROUP to WO. LABORGROUP
> Could anybody please show me how to do this or point me to the right
> direction, any help will be greatly appreciated.
> Thanks!
> Martin

I don't really understand your description - are you saying that when you
insert a row into WO you want to update PROBLEMCODE and LABORGROUP with
corresponding values from the PM table, joined on the PMNUM column?

In future, please post table structure as DDL, ie. CREATE TABLE statements,
so it's clear what your keys and constraints are, along with INSERT
statements for sample data - this is much clearer than a description.

See below for sample code - it may be incorrect, but hopefully it will get
you started, at least.

Simon

create trigger dbo.ITR_WO
on dbo.WO after insert
as
begin
if @.@.rowcount = 0
return

update
dbo.WO
set
LABORGROUP=p.LABORGROUP,
PROBLEMCODE=p.PROBLEMCODE
from
dbo.PM p
join dbo.WO w
on i.PMNUM = WO.PMNUM
end|||You need to create an Insert Trigger in the WO table to do an update
joining the PM table when the keys are the same. Let me know if you
need the syntex..!|||On 4 Jan 2005 10:04:18 -0800, Martin wrote:

(snip)
>Could anybody please show me how to do this or point me to the right
>direction, any help will be greatly appreciated.

Hi Martin,

Simon already gave you some code that might do what you want (but beware
of unexpected results if one row in PM is matched by more than one row in
WO), but I'd like to question the reason for what you want to do.

The table names WO and PM don't reveal anything about your business, of
course, so I might be wrong - but from the looks of it, your design is
violating third normal form. Are you sure that you wouldn't be better off
removing the problemcode and laborgroup from the WO table, and joining the
PM table in when you need to report these properties for a given WOnum?

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Simon Hayes (sql@.hayes.ch) writes:
> create trigger dbo.ITR_WO
> on dbo.WO after insert
> as
> begin
> if @.@.rowcount = 0
> return
> update
> dbo.WO
> set
> LABORGROUP=p.LABORGROUP,
> PROBLEMCODE=p.PROBLEMCODE
> from
> dbo.PM p
> join dbo.WO w
> on i.PMNUM = WO.PMNUM
> end

The line

join dbo.WO w

ought to be

join inserted w

Simon knows this as well, but for Martin this call for an explanation.
"inserted" is a virtual table that holds the rows inserted by the
INSERT statement. Note that the trigger fires once per statement, and
the virttual table, thus can have many rows.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns95D517178C9Yazorman@.127.0.0.1...
> Simon Hayes (sql@.hayes.ch) writes:
>> create trigger dbo.ITR_WO
>> on dbo.WO after insert
>> as
>> begin
>> if @.@.rowcount = 0
>> return
>>
>> update
>> dbo.WO
>> set
>> LABORGROUP=p.LABORGROUP,
>> PROBLEMCODE=p.PROBLEMCODE
>> from
>> dbo.PM p
>> join dbo.WO w
>> on i.PMNUM = WO.PMNUM
>> end
> The line
> join dbo.WO w
> ought to be
> join inserted w
> Simon knows this as well, but for Martin this call for an explanation.
> "inserted" is a virtual table that holds the rows inserted by the
> INSERT statement. Note that the trigger fires once per statement, and
> the virttual table, thus can have many rows.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

Oops, my mistake - thanks for the correction, Erland.

Simon|||Hello Guys,

Thank you for all your feedback!.

Below is the trigger that I'm using and Hugo is right there are many
PMs on WOs so it updates all the WO that matches the PMNUM..

CREATE TRIGGER GENERATE_PM_WO ON [workorder]
FOR INSERT
AS
BEGIN
IF @.@.rowcount = 0
RETURN
UPDATE [workorder]
SET
woassignmntqueueid = P.PM2,
problemcode = P.PM1
FROM
[pm]AS P join inserted AS I
on P.PMNUM = I.PMNUM
END

Any way, here is now the situation, these fields, woassignmntqueueid
and problemcode are required (NOT NULL ALLOWED) on the workorder table;
so, this trigger never executes. What do I need to do, to update only
the current workorder passing pm.pm2 and pm.pm1 to woassignmntqueueid
and problemcode.

Any help will be appreciated.

Thanks!

Martin

Hugo Kornelis wrote:
> On 4 Jan 2005 10:04:18 -0800, Martin wrote:
> (snip)
> >Could anybody please show me how to do this or point me to the right
> >direction, any help will be greatly appreciated.
> Hi Martin,
> Simon already gave you some code that might do what you want (but
beware
> of unexpected results if one row in PM is matched by more than one
row in
> WO), but I'd like to question the reason for what you want to do.
> The table names WO and PM don't reveal anything about your business,
of
> course, so I might be wrong - but from the looks of it, your design
is
> violating third normal form. Are you sure that you wouldn't be better
off
> removing the problemcode and laborgroup from the WO table, and
joining the
> PM table in when you need to report these properties for a given
WOnum?
> Best, Hugo|||On 20 Jan 2005 09:07:57 -0800, Martin wrote:

>Hello Guys,
>Thank you for all your feedback!.
>Below is the trigger that I'm using and Hugo is right there are many
>PMs on WOs so it updates all the WO that matches the PMNUM..
(sniup trigger code)

Hi Martin,

The code you posted is even worse: it will update ALL rows currently in
the workorder table. All these rows will have their woassignmntqueueid and
their problemcode set to PM2 and PM1 from a pm row that matches one of the
inserted rows - and if multiple rows are inserted, the trigger will just
choose one, semi-randomly.

I'm quite sure that this is not what you want - but I have no idea what
you do want.

>Any way, here is now the situation, these fields, woassignmntqueueid
>and problemcode are required (NOT NULL ALLOWED) on the workorder table;
>so, this trigger never executes.

This conclusion is wrong. Whether these rows allow NULLS or not has
nothing to do with the firing of this trigger. As soon as an INSERT
statement is run against the workorder table, this trigger *WILL* run, and
it *WILL* attempt to update *all* rows in workorder.

Of course, if the chosen value for either woassignmntqueueid or
problemcode happens to be NULL, the update will fail, causing an error in
the trigger and a rollback of the entire transaction (including the insert
statement that caused the trigger to fire). But the trigger DOES execute!

> What do I need to do, to update only
>the current workorder passing pm.pm2 and pm.pm1 to woassignmntqueueid
>and problemcode.

I'mm sorry, but your narrative is not sufficient to explain your exact
requirements. I suggest you post
* The structure of all related tables (as CREATE TABLE statements,
including datatypes, constraints and properties; irrelevant columns may be
omitted, especially if there are lots of them),
* Some illustrative sample data (as INSERT statements, so that I can use
cut and paste to run the code in Query Analyzer and recreate your sample
data on my test database),
* The required output, and
* A concise description of the business problem you're trying to solve.

Check out this site as well: http://www.aspfaq.com/5006.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hello Hugo,

Thanks for your help I really appreciate the time and effort that you
give to UNKNOWN people!

I have this program that helps us track the Work Orders request for
this site. Every day we use a WO Screen to enter the routine request
from our clients; all the data goes to the workorder Table and we have
the problemcode and the woassignmntqueueid fields set as required (Not
Null Allowed). This process works perfect we do not have any problems
with this daily process.

My Problem is that this program also have a PM (Preventive Maintenance)
screen that I have to use every February to generate all the PM work
orders for the whole year; these work orders or records are also
created in the workorder table and of course the data from the daily
work orders are different from the PM work orders and that is the
reason this program doesn't populate these to fields. Also in a few
occasion during the year I have to create new PM work order for
equipment that is added or replace on this site.

When I generate the PM for the site I have to do it after hours, I
configure the database and make those two fields to allow null values
then I run update queries to populate them, and then I reconfigure the
DB so these two fields are required again. The other problem is that if
we add or replace a piece of equipment and we need a PM work order
immediately, I can't happen.

That is the main reason I need to create this trigger.

My intention is when I'm on the PM screen and run the automate
routine to Generate or Create PM work order, this trigger will pass the
data from the PM table to the workorder table

Workorder.problemcode = pm.pm1
Workorder.woassignmntqueueid = pm.pm2

PM Table
Pmnun Description Pm1 Pm2
Hvac001 Monthly A/C Unit PM HVAC MAINT

Workorder Table
Wonum Description Pmnum problemcode woassignmntqueueid
1234567 Monthly A/C Unit PM Hvac001 HVAC MAINT

I have about 500 PM records and each can have many records on the
workorder table. The PMNUM field is the key field on the PM table and
a foreign key on the workorder table.

Below is the code to create and populate the workorder and PM table.

I really appreciate all your help.

Thanks!

Martin

create table workorder (
wonum varchar (10) not null ,
parent varchar (10) null ,
status varchar (8) not null ,
statusdate datetime not null ,
worktype varchar (5) null ,
leadcraft varchar (8) null ,
description varchar (50) null ,
eqnum varchar (8) null ,
location varchar (8) null ,
jpnum varchar (10) null ,
faildate datetime null ,
changeby varchar (18) null ,
changedate datetime null ,
estdur double precision not null ,
estlabhrs double precision not null ,
estmatcost decimal(10,2) not null ,
estlabcost decimal(10,2) not null ,
esttoolcost decimal(10,2) not null ,
pmnum varchar (8) null ,
actlabhrs double precision not null ,
actmatcost decimal(10,2) not null ,
actlabcost decimal(10,2) not null ,
acttoolcost decimal(10,2) not null ,
haschildren varchar (1) not null ,
outlabcost decimal(10,2) not null ,
outmatcost decimal(10,2) not null ,
outtoolcost decimal(10,2) not null ,
historyflag varchar (1) not null ,
contract varchar (8) null ,
wopriority integer null ,
wopm6 varchar (10) null ,
wopm7 decimal(15,2) null ,
targcompdate datetime null ,
targstartdate datetime null ,
woeq1 varchar (10) null ,
woeq2 varchar (10) null ,
woeq3 varchar (10) null ,
woeq4 varchar (10) null ,
woeq5 decimal(10,2) null ,
woeq6 datetime null ,
woeq7 decimal(15,2) null ,
woeq8 varchar (10) null ,
woeq9 varchar (10) null ,
woeq10 varchar (10) null ,
woeq11 varchar (10) null ,
woeq12 decimal(10,2) null ,
wo1 varchar (10) null ,
wo2 varchar (10) null ,
wo3 varchar (10) null ,
wo4 varchar (10) null ,
wo5 varchar (10) null ,
wo6 varchar (10) null ,
wo7 varchar (10) null ,
wo8 varchar (10) null ,
wo9 varchar (10) null ,
wo10 varchar (10) null ,
ldkey integer null ,
reportedby varchar (18) null ,
reportdate datetime null ,
phone varchar (20) null ,
problemcode varchar (8) not null ,
calendar varchar (8) null ,
interruptable varchar (1) null ,
downtime varchar (1) null ,
actstart datetime null ,
actfinish datetime null ,
schedstart datetime null ,
schedfinish datetime null ,
remdur double precision null ,
crewid varchar (8) null ,
supervisor varchar (8) null ,
woeq13 datetime null ,
woeq14 decimal(15,2) null ,
wopm1 varchar (10) null ,
wopm2 varchar (10) null ,
wopm3 varchar (10) null ,
wopm4 decimal(10,2) null ,
wopm5 varchar (10) null ,
wojp1 varchar (10) null ,
wojp2 varchar (10) null ,
wojp3 varchar (10) null ,
wojp4 decimal(10,2) null ,
wojp5 datetime null ,
wol1 varchar (10) null ,
wol2 varchar (10) null ,
wol3 decimal(10,2) null ,
wol4 datetime null ,
wolablnk varchar (8) null ,
respondby datetime null ,
eqlocpriority integer null ,
calcpriority integer null ,
chargestore varchar (1) not null ,
failurecode varchar (8) null ,
wolo1 varchar (10) null ,
wolo2 varchar (10) null ,
wolo3 varchar (10) null ,
wolo4 varchar (10) null ,
wolo5 varchar (10) null ,
wolo6 decimal(10,2) null ,
wolo7 datetime null ,
wolo8 decimal(15,2) null ,
wolo9 varchar (10) null ,
wolo10 integer null ,
glaccount varchar (20) null ,
estservcost decimal(10,2) not null ,
actservcost decimal(10,2) not null ,
disabled varchar (1) null ,
estatapprlabhrs double precision not null ,
estatapprlabcost decimal(10,2) not null ,
estatapprmatcost decimal(10,2) not null ,
estatapprtoolcost decimal(10,2) not null ,
estatapprservcost decimal(10,2) not null ,
wosequence integer null ,
hasfollowupwork varchar (1) not null ,
worts1 varchar (10) null ,
worts2 varchar (10) null ,
worts3 varchar (10) null ,
worts4 datetime null ,
worts5 decimal(15,2) null ,
wfid integer null ,
wfactive varchar (1) not null ,
sourcesysid varchar (10) null ,
ownersysid varchar (10) null ,
followupfromwonum varchar (10) null ,
pmduedate datetime null ,
pmextdate datetime null ,
pmnextduedate datetime null ,
viewwoasoper varchar (1) not null ,
woassignmntqueueid varchar (8) not null ,
worklocation varchar (8) null ,
wowq1 varchar (1) null ,
wowq2 varchar (1) null ,
wowq3 varchar (1) null ,
wojp6 varchar (10) null ,
wojp7 varchar (10) null ,
wojp8 varchar (10) null ,
wojp9 decimal(10,2) null ,
wojp10 datetime null ,
wo11 decimal(10,2) null ,
wo12 decimal(10,2) null ,
wo13 datetime null ,
wo14 datetime null ,
wo15 decimal(15,2) null ,
wo16 decimal(15,2) null ,
wo17 varchar (10) null ,
wo18 varchar (10) null ,
wo19 integer null ,
wo20 varchar (1) null ,
externalrefid varchar (10) null ,
apiseq varchar (50) null ,
interid varchar (50) null ,
migchangeid varchar (50) null ,
sendersysid varchar (50) null ,
expdone varchar (25) null ,
fincntrlid varchar (8) null ,
generatedforpo varchar (8) null ,
genforpolineid integer null ,
rowstamp timestamp
)
go

insert into workorder
( wonum, parent, status, statusdate, worktype, leadcraft, description,
eqnum, location,
jpnum, faildate, changeby, changedate, estdur, estlabhrs, estmatcost,
estlabcost, esttoolcost,
pmnum, actlabhrs, actmatcost, actlabcost, acttoolcost, haschildren,
outlabcost, outmatcost, outtoolcost,
historyflag, contract, wopriority, wopm6, wopm7, targcompdate,
targstartdate, woeq1, woeq2,
woeq3, woeq4, woeq5, woeq6, woeq7, woeq8, woeq9, woeq10, woeq11,
woeq12, wo1, wo2, wo3, wo4, wo5, wo6, wo7, wo8,
wo9, wo10, ldkey, reportedby, reportdate, phone, problemcode,
calendar, interruptable,
downtime, actstart, actfinish, schedstart, schedfinish, remdur,
crewid, supervisor, woeq13,
woeq14, wopm1, wopm2, wopm3, wopm4, wopm5, wojp1, wojp2, wojp3,
wojp4, wojp5, wol1, wol2, wol3, wol4, wolablnk, respondby,
eqlocpriority,
calcpriority, chargestore, failurecode, wolo1, wolo2, wolo3, wolo4,
wolo5, wolo6,
wolo7, wolo8, wolo9, wolo10, glaccount, estservcost, actservcost,
disabled, estatapprlabhrs,
estatapprlabcost, estatapprmatcost, estatapprtoolcost,
estatapprservcost, wosequence, hasfollowupwork, worts1, worts2, worts3,
worts4, worts5, wfid, wfactive, sourcesysid, ownersysid,
followupfromwonum, pmduedate, pmextdate,
pmnextduedate, viewwoasoper, woassignmntqueueid, worklocation, wowq1,
wowq2, wowq3, wojp6, wojp7,
wojp8, wojp9, wojp10, wo11, wo12, wo13, wo14, wo15, wo16,
wo17, wo18, wo19, wo20, externalrefid, apiseq, interid, migchangeid,
sendersysid,
expdone, fincntrlid, generatedforpo, genforpolineid)
values
( '7333', '7330', 'WAPPR', '1998-09-23 22:17:00', 'CP', NULL, 'Install
turntable', NULL, 'NEEDHAM',
NULL, NULL, 'MAXIMO', '1999-03-29 19:48:00', 16, 64, 0, 1172, 34,
NULL, 0, 0, 0, 0, 'N', 0, 0, 0,
'N', NULL, 9, NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, 'MAXIMO', '1998-09-23 22:17:00', NULL, 'MAINT',
NULL, NULL,
NULL, NULL, NULL, '1999-03-29 0:00:00', '1999-03-29 8:00:00', NULL,
NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
NULL, 'N', NULL, NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, '6000300?', 0, 0, NULL, 0,
0, 0, 0, 0, 2, 'N', NULL, NULL, NULL,
NULL, NULL, 0, 'N', NULL, NULL, NULL, NULL, NULL,
NULL, 'N', 'PM', NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL)
go

create table pm (
pmnum varchar (8) not null ,
description varchar (50) null ,
eqnum varchar (8) null ,
firstdate datetime null ,
lastcompdate datetime null ,
laststartdate datetime null ,
usetargetdate varchar (1) not null ,
lastmeterreading decimal(15,2) not null ,
lastmeterdate datetime null ,
frequency integer not null ,
meterfrequency decimal(15,2) not null ,
pmcounter integer not null ,
priority integer not null ,
worktype varchar (5) null ,
jpnum varchar (10) null ,
jpseqinuse varchar (1) not null ,
nextdate datetime null ,
pm17 varchar (10) null ,
pm18 decimal(15,2) null ,
changedate datetime not null ,
changeby varchar (18) not null ,
pmeq1 varchar (10) null ,
pm1 varchar (8) not null ,
pm2 varchar (8) not null ,
pm3 varchar (10) null ,
pm4 datetime null ,
pm5 decimal(15,2) null ,
ldkey integer null ,
supervisor varchar (8) null ,
calendar varchar (8) null ,
crewid varchar (8) null ,
interruptable varchar (1) null ,
downtime varchar (1) null ,
pm6 varchar (10) null ,
pm7 varchar (10) null ,
pm8 varchar (10) null ,
pm9 decimal(10,2) null ,
pm10 varchar (10) null ,
pmeq2 datetime null ,
pmeq3 decimal(15,2) null ,
pmjp1 varchar (10) null ,
pmjp2 varchar (10) null ,
pmjp3 varchar (10) null ,
pmjp4 decimal(10,2) null ,
pmjp5 datetime null ,
glaccount varchar (20) null ,
location varchar (8) null ,
storeloc varchar (8) null ,
parent varchar (8) null ,
haschildren varchar (1) not null ,
wosequence integer null ,
usefrequency varchar (1) not null ,
route varchar (8) null ,
frequnit varchar (8) not null ,
meterfrequency2 decimal(15,2) not null ,
lastmeterreading2 decimal(15,2) not null ,
lastmeterdate2 datetime null ,
leadtime integer null ,
extdate datetime null ,
adjnextdue varchar (1) null ,
pm11 varchar (10) null ,
pm12 varchar (10) null ,
pm13 varchar (10) null ,
pm14 decimal(10,2) null ,
pm15 integer null ,
pm16 varchar (1) null ,
masterpm varchar (8) null ,
overridemasterupd varchar (1) not null ,
ismasterpm varchar (1) not null ,
masterpmitemnum varchar (30) null ,
applymasterpmtoeq varchar (1) not null ,
applymasterpmtoloc varchar (1) not null ,
updtimebasedfreq varchar (1) not null ,
updstartdate varchar (1) not null ,
updmeter1 varchar (1) not null ,
updmeter2 varchar (1) not null ,
updjpsequence varchar (1) not null ,
updextdate varchar (1) not null ,
updseasonaldates varchar (1) not null ,
wostatus varchar (8) not null ,
seasonstartday smallint null ,
seasonstartmonth varchar (16) null ,
seasonendday smallint null ,
seasonendmonth varchar (16) null ,
pmjp6 varchar (10) null ,
pmjp7 varchar (10) null ,
pmjp8 varchar (10) null ,
pmjp9 decimal(10,2) null ,
pmjp10 datetime null ,
rowstamp timestamp
)
go

insert into pm
( pmnum, description, eqnum, firstdate, lastcompdate, laststartdate,
usetargetdate, lastmeterreading, lastmeterdate,
frequency, meterfrequency, pmcounter, priority, worktype, jpnum,
jpseqinuse, nextdate, pm17,
pm18, changedate, changeby, pmeq1, pm1, pm2, pm3, pm4, pm5,
ldkey, supervisor, calendar, crewid, interruptable, downtime, pm6,
pm7, pm8,
pm9, pm10, pmeq2, pmeq3, pmjp1, pmjp2, pmjp3, pmjp4, pmjp5,
glaccount, location, storeloc, parent, haschildren, wosequence,
usefrequency, route, frequnit,
meterfrequency2, lastmeterreading2, lastmeterdate2, leadtime, extdate,
adjnextdue, pm11, pm12, pm13,
pm14, pm15, pm16, masterpm, overridemasterupd, ismasterpm,
masterpmitemnum, applymasterpmtoeq, applymasterpmtoloc,
updtimebasedfreq, updstartdate, updmeter1, updmeter2, updjpsequence,
updextdate, updseasonaldates, wostatus, seasonstartday,
seasonstartmonth, seasonendday, seasonendmonth, pmjp6, pmjp7, pmjp8,
pmjp9, pmjp10)
values
( 'PM-CONV2', 'Conveyor Overhaul- Conveyor #2', '12700', '1999-03-03
00:00:00', '1996-11-13 00:00:00', '1999-03-30 00:00:00', 'Y', 0, NULL,
90, 0, 1, 8, 'PM', 'JP1314A', 'Y', '1999-06-28 00:00:00', '332',
NULL, '1999-03-30 18:40:00', 'MAXIMO', NULL, 'MAINT', 'PM', NULL,
NULL, NULL,
NULL, NULL, NULL, NULL, 'N', 'Y', NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, 'CENTRAL', NULL, 'N', NULL, 'N', NULL, 'DAYS',
0, 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, 'N', 'N', NULL, 'Y', 'Y',
'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'WSCH', NULL,
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)
go

Hugo Kornelis wrote:
> On 20 Jan 2005 09:07:57 -0800, Martin wrote:
> >Hello Guys,
> >Thank you for all your feedback!.
> >Below is the trigger that I'm using and Hugo is right there are many
> >PMs on WOs so it updates all the WO that matches the PMNUM..
> (sniup trigger code)
> Hi Martin,
> The code you posted is even worse: it will update ALL rows currently
in
> the workorder table. All these rows will have their
woassignmntqueueid and
> their problemcode set to PM2 and PM1 from a pm row that matches one
of the
> inserted rows - and if multiple rows are inserted, the trigger will
just
> choose one, semi-randomly.
> I'm quite sure that this is not what you want - but I have no idea
what
> you do want.
>
> >Any way, here is now the situation, these fields, woassignmntqueueid
> >and problemcode are required (NOT NULL ALLOWED) on the workorder
table;
> >so, this trigger never executes.
> This conclusion is wrong. Whether these rows allow NULLS or not has
> nothing to do with the firing of this trigger. As soon as an INSERT
> statement is run against the workorder table, this trigger *WILL*
run, and
> it *WILL* attempt to update *all* rows in workorder.
> Of course, if the chosen value for either woassignmntqueueid or
> problemcode happens to be NULL, the update will fail, causing an
error in
> the trigger and a rollback of the entire transaction (including the
insert
> statement that caused the trigger to fire). But the trigger DOES
execute!
>
> > What do I need to do, to update only
> >the current workorder passing pm.pm2 and pm.pm1 to
woassignmntqueueid
> >and problemcode.
> I'mm sorry, but your narrative is not sufficient to explain your
exact
> requirements. I suggest you post
> * The structure of all related tables (as CREATE TABLE statements,
> including datatypes, constraints and properties; irrelevant columns
may be
> omitted, especially if there are lots of them),
> * Some illustrative sample data (as INSERT statements, so that I can
use
> cut and paste to run the code in Query Analyzer and recreate your
sample
> data on my test database),
> * The required output, and
> * A concise description of the business problem you're trying to
solve.
> Check out this site as well: http://www.aspfaq.com/5006.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On 24 Jan 2005 05:31:04 -0800, Martin wrote:

(snip)
>When I generate the PM for the site I have to do it after hours, I
>configure the database and make those two fields to allow null values
>then I run update queries to populate them, and then I reconfigure the
>DB so these two fields are required again. The other problem is that if
>we add or replace a piece of equipment and we need a PM work order
>immediately, I can't happen.
>That is the main reason I need to create this trigger.
>My intention is when I'm on the PM screen and run the automate
>routine to Generate or Create PM work order, this trigger will pass the
>data from the PM table to the workorder table

Hi Martin,

Based on what I read, it appears that you're fighting the symptoms instead
of addressing the cause. It seems to me that the problem is that the code
that generates work orders from PM entries fails to provide the
problemcode and assignmentqueue, even though they ARE available in the PM
table. Could you post the code that generates new work orders from the
rows in the PM table? I guess that THAT is where the real key to solving
your problem lies.

>Workorder.problemcode = pm.pm1
>Workorder.woassignmntqueueid = pm.pm2
>PM Table
>Pmnun Description Pm1 Pm2
>Hvac001 Monthly A/C Unit PM HVAC MAINT
>Workorder Table
>Wonum Description Pmnum problemcode woassignmntqueueid
>1234567 Monthly A/C Unit PM Hvac001 HVAC MAINT
>I have about 500 PM records and each can have many records on the
>workorder table. The PMNUM field is the key field on the PM table and
>a foreign key on the workorder table.

In the mean time, the above contains the info I need to help you with the
trigger. Since there is a foreign key from Workorder to PM, it's possible
to find the one and only PM row that a workorder should be coupled to and
take PM1 and PM2 from that row.

The code below won't update the assignment queue or problemcode values if
no Pmnum is specified in the new row. I assume that Omnum is only present
if a workorder is generated for preventive maintenance and that assignment
queue and workorder should not be changed for other work orders.

If you need the ability to override the problemcode and assignmentqueue
from the PM tables, you need to make two changes:
* change your frontend code so that overriding values for problemcode and
assignment queue can be included in the INSERT statement
* change the SET clauses to (using problemcode as an example)
SET problemcode = COALESCE (w.problemcode, P.PM1)
this will ensure that the value entered is retained, bot if no value is
entered (the value is NULL), it will be replaced by the PM1 value.

CREATE TRIGGER GENERATE_PM_WO ON [workorder]
FOR INSERT
AS
BEGIN
IF @.@.rowcount = 0
RETURN
UPDATE w
SET woassignmntqueueid = P.PM2,
problemcode = P.PM1
FROM workorder AS w
INNER JOIN pm AS p
ON p.Pmnum = w.Pmnum
INNER JOIN inserted AS i
ON i.Wonum = w.Wonum
-- Note - the inner join to inserted ensures only new rows are affected.
-- This could just as well have been written as an EXISTS or IN subquery.
END

(untested)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Insert Trigger causes new record to disappear

Hello, I am using the following trigger. After I insert a new record a new
record into the table, the record disappears and a previous record will show
up as the last record. If I change it to just an update trigger, it works
fine. My question is why would an insert trigger cause this anomaly?
Thanks, Steven
CREATE TRIGGER tgrBusinessEmployee_i ON dbo.tblBusinessEmployee
FOR INSERT
AS
INSERT into tlogBusinessEmployee
(EmployeeID, BusinessLocation, LastName, FirstName, NickName,
WebPassword, Title, --CurriculumVitae,
ContactType, ContactOrder, Email, Phone, TollFree, Fax, WWW,
yn_ActiveEmployee, yn_PublishToWeb,
yn_LockOut, yn_Remove, DateEntered, SortKey, ContactID, FirmID,
LogProcess)
SELECT
Employee_ID, BusinessLocation, LastName, FirstName, NickName,
WebPassword, Title, --Convert(varchar(5000),CurriculumVitae),
ContactType, ContactOrder, Email, Phone, TollFree, Fax, WWW,
yn_ActiveEmployee, yn_PublishToWeb,
yn_LockOut, yn_Remove, DateEntered, SortKey, ContactID, FirmID,
'InsertUpdate'
FROM inserted
Steven,
Perhaps the insert into tlogBusinessEmployee inside the trigger is failing,
causing the insert into tblBusinessEmployee to fail.
If you add error handling to the trigger, you can detect an insert error and
debug (through a print statement) what the trigger is actually doing.
Alternatively, you can step through an insert in Query Analyzer or Visual
Studio, and debug the trigger.
Hope this helps,
Ron
Ron Talmage
SQL Server MVP
"Steven K0" <stroy@.api.com> wrote in message
news:efT2ywFnEHA.3392@.TK2MSFTNGP15.phx.gbl...
> Hello, I am using the following trigger. After I insert a new record a
new
> record into the table, the record disappears and a previous record will
show
> up as the last record. If I change it to just an update trigger, it works
> fine. My question is why would an insert trigger cause this anomaly?
> Thanks, Steven
> CREATE TRIGGER tgrBusinessEmployee_i ON dbo.tblBusinessEmployee
> FOR INSERT
> AS
> INSERT into tlogBusinessEmployee
> (EmployeeID, BusinessLocation, LastName, FirstName, NickName,
> WebPassword, Title, --CurriculumVitae,
> ContactType, ContactOrder, Email, Phone, TollFree, Fax, WWW,
> yn_ActiveEmployee, yn_PublishToWeb,
> yn_LockOut, yn_Remove, DateEntered, SortKey, ContactID, FirmID,
> LogProcess)
> SELECT
> Employee_ID, BusinessLocation, LastName, FirstName, NickName,
> WebPassword, Title, --Convert(varchar(5000),CurriculumVitae),
> ContactType, ContactOrder, Email, Phone, TollFree, Fax, WWW,
> yn_ActiveEmployee, yn_PublishToWeb,
> yn_LockOut, yn_Remove, DateEntered, SortKey, ContactID, FirmID,
> 'InsertUpdate'
> FROM inserted
>
|||Sounds like a problem with the client code. Are you using an identity
column as the PK for this table? Does the log table also have an identity
column?
"Steven K0" <stroy@.api.com> wrote in message
news:efT2ywFnEHA.3392@.TK2MSFTNGP15.phx.gbl...
> Hello, I am using the following trigger. After I insert a new record a
new
> record into the table, the record disappears and a previous record will
show
> up as the last record. If I change it to just an update trigger, it works
> fine. My question is why would an insert trigger cause this anomaly?
> Thanks, Steven
> CREATE TRIGGER tgrBusinessEmployee_i ON dbo.tblBusinessEmployee
> FOR INSERT
> AS
> INSERT into tlogBusinessEmployee
> (EmployeeID, BusinessLocation, LastName, FirstName, NickName,
> WebPassword, Title, --CurriculumVitae,
> ContactType, ContactOrder, Email, Phone, TollFree, Fax, WWW,
> yn_ActiveEmployee, yn_PublishToWeb,
> yn_LockOut, yn_Remove, DateEntered, SortKey, ContactID, FirmID,
> LogProcess)
> SELECT
> Employee_ID, BusinessLocation, LastName, FirstName, NickName,
> WebPassword, Title, --Convert(varchar(5000),CurriculumVitae),
> ContactType, ContactOrder, Email, Phone, TollFree, Fax, WWW,
> yn_ActiveEmployee, yn_PublishToWeb,
> yn_LockOut, yn_Remove, DateEntered, SortKey, ContactID, FirmID,
> 'InsertUpdate'
> FROM inserted
>
|||Scott,
The answer is yes to both questions, but I am not using the Employee_ID as
the PK and identity column in the log table:
CREATE TABLE [tblBusinessEmployee] (
[Employee_ID] [int] IDENTITY (1, 1) NOT NULL ,
CONSTRAINT [pk_BusinessEmployee] PRIMARY KEY CLUSTERED
(
[Employee_ID]
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
CREATE TABLE [tlogBusinessEmployee] (
[EmployeeLog_ID] [int] IDENTITY (1, 1) NOT NULL ,
[EmployeeID] [int] NULL ,
CONSTRAINT [pk_LogBusinessEmployee] PRIMARY KEY CLUSTERED
(
[EmployeeLog_ID]
) ON [PRIMARY]
) ON [PRIMARY]
INSERT into tlogBusinessEmployee
(EmployeeID, BusinessLocation, LastName, FirstName, NickName,
WebPassword, Title, --CurriculumVitae,
ContactType, ContactOrder, Email, Phone, TollFree, Fax, WWW,
yn_ActiveEmployee, yn_PublishToWeb,
yn_LockOut, yn_Remove, DateEntered, SortKey, ContactID, FirmID,
LogProcess)
SELECT
Employee_ID, BusinessLocation, LastName, FirstName, NickName,
WebPassword, Title, --Convert(varchar(5000),CurriculumVitae),
ContactType, ContactOrder, Email, Phone, TollFree, Fax, WWW,
yn_ActiveEmployee, yn_PublishToWeb,
yn_LockOut, yn_Remove, DateEntered, SortKey, ContactID, FirmID,
'InsertUpdate'
FROM inserted
"Scott Morris" <bogus@.bogus.com> wrote in message
news:e9BhBwLnEHA.1236@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Sounds like a problem with the client code. Are you using an identity
> column as the PK for this table? Does the log table also have an identity
> column?
> "Steven K0" <stroy@.api.com> wrote in message
> news:efT2ywFnEHA.3392@.TK2MSFTNGP15.phx.gbl...
> new
> show
works[vbcol=seagreen]
|||As I indicated, the problem is in the client application. Most likely, the
application is using @.@.identity to identify the ID of the inserted employee
row, which would be incorrect in this case (this is supported by the remark
that the application works correctly when the insert trigger logic is
removed). use scope_identity() if using sql2k. Use of the profiler may help
to identify issues with the conversation between the client and the server.
"Steven K" <skaper@.troop.com> wrote in message
news:e352JZOnEHA.952@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Scott,
> The answer is yes to both questions, but I am not using the Employee_ID as
> the PK and identity column in the log table:
> CREATE TABLE [tblBusinessEmployee] (
> [Employee_ID] [int] IDENTITY (1, 1) NOT NULL ,
> CONSTRAINT [pk_BusinessEmployee] PRIMARY KEY CLUSTERED
> (
> [Employee_ID]
> ) ON [PRIMARY]
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
>
> CREATE TABLE [tlogBusinessEmployee] (
> [EmployeeLog_ID] [int] IDENTITY (1, 1) NOT NULL ,
> [EmployeeID] [int] NULL ,
> CONSTRAINT [pk_LogBusinessEmployee] PRIMARY KEY CLUSTERED
> (
> [EmployeeLog_ID]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
>
>
> INSERT into tlogBusinessEmployee
> (EmployeeID, BusinessLocation, LastName, FirstName, NickName,
> WebPassword, Title, --CurriculumVitae,
> ContactType, ContactOrder, Email, Phone, TollFree, Fax, WWW,
> yn_ActiveEmployee, yn_PublishToWeb,
> yn_LockOut, yn_Remove, DateEntered, SortKey, ContactID, FirmID,
> LogProcess)
> SELECT
> Employee_ID, BusinessLocation, LastName, FirstName, NickName,
> WebPassword, Title, --Convert(varchar(5000),CurriculumVitae),
> ContactType, ContactOrder, Email, Phone, TollFree, Fax, WWW,
> yn_ActiveEmployee, yn_PublishToWeb,
> yn_LockOut, yn_Remove, DateEntered, SortKey, ContactID, FirmID,
> 'InsertUpdate'
> FROM inserted
>
> "Scott Morris" <bogus@.bogus.com> wrote in message
> news:e9BhBwLnEHA.1236@.TK2MSFTNGP09.phx.gbl...
identity[vbcol=seagreen]
a[vbcol=seagreen]
will
> works
>

Insert Trigger causes new record to disappear

Hello, I am using the following trigger. After I insert a new record a new
record into the table, the record disappears and a previous record will show
up as the last record. If I change it to just an update trigger, it works
fine. My question is why would an insert trigger cause this anomaly?
Thanks, Steven
CREATE TRIGGER tgrBusinessEmployee_i ON dbo.tblBusinessEmployee
FOR INSERT
AS
INSERT into tlogBusinessEmployee
(EmployeeID, BusinessLocation, LastName, FirstName, NickName,
WebPassword, Title, --CurriculumVitae,
ContactType, ContactOrder, Email, Phone, TollFree, Fax, WWW,
yn_ActiveEmployee, yn_PublishToWeb,
yn_LockOut, yn_Remove, DateEntered, SortKey, ContactID, FirmID,
LogProcess)
SELECT
Employee_ID, BusinessLocation, LastName, FirstName, NickName,
WebPassword, Title, --Convert(varchar(5000),CurriculumVitae),
ContactType, ContactOrder, Email, Phone, TollFree, Fax, WWW,
yn_ActiveEmployee, yn_PublishToWeb,
yn_LockOut, yn_Remove, DateEntered, SortKey, ContactID, FirmID,
'InsertUpdate'
FROM insertedSteven,
Perhaps the insert into tlogBusinessEmployee inside the trigger is failing,
causing the insert into tblBusinessEmployee to fail.
If you add error handling to the trigger, you can detect an insert error and
debug (through a print statement) what the trigger is actually doing.
Alternatively, you can step through an insert in Query Analyzer or Visual
Studio, and debug the trigger.
Hope this helps,
Ron
--
Ron Talmage
SQL Server MVP
"Steven K0" <stroy@.api.com> wrote in message
news:efT2ywFnEHA.3392@.TK2MSFTNGP15.phx.gbl...
> Hello, I am using the following trigger. After I insert a new record a
new
> record into the table, the record disappears and a previous record will
show
> up as the last record. If I change it to just an update trigger, it works
> fine. My question is why would an insert trigger cause this anomaly?
> Thanks, Steven
> CREATE TRIGGER tgrBusinessEmployee_i ON dbo.tblBusinessEmployee
> FOR INSERT
> AS
> INSERT into tlogBusinessEmployee
> (EmployeeID, BusinessLocation, LastName, FirstName, NickName,
> WebPassword, Title, --CurriculumVitae,
> ContactType, ContactOrder, Email, Phone, TollFree, Fax, WWW,
> yn_ActiveEmployee, yn_PublishToWeb,
> yn_LockOut, yn_Remove, DateEntered, SortKey, ContactID, FirmID,
> LogProcess)
> SELECT
> Employee_ID, BusinessLocation, LastName, FirstName, NickName,
> WebPassword, Title, --Convert(varchar(5000),CurriculumVitae),
> ContactType, ContactOrder, Email, Phone, TollFree, Fax, WWW,
> yn_ActiveEmployee, yn_PublishToWeb,
> yn_LockOut, yn_Remove, DateEntered, SortKey, ContactID, FirmID,
> 'InsertUpdate'
> FROM inserted
>|||Sounds like a problem with the client code. Are you using an identity
column as the PK for this table? Does the log table also have an identity
column?
"Steven K0" <stroy@.api.com> wrote in message
news:efT2ywFnEHA.3392@.TK2MSFTNGP15.phx.gbl...
> Hello, I am using the following trigger. After I insert a new record a
new
> record into the table, the record disappears and a previous record will
show
> up as the last record. If I change it to just an update trigger, it works
> fine. My question is why would an insert trigger cause this anomaly?
> Thanks, Steven
> CREATE TRIGGER tgrBusinessEmployee_i ON dbo.tblBusinessEmployee
> FOR INSERT
> AS
> INSERT into tlogBusinessEmployee
> (EmployeeID, BusinessLocation, LastName, FirstName, NickName,
> WebPassword, Title, --CurriculumVitae,
> ContactType, ContactOrder, Email, Phone, TollFree, Fax, WWW,
> yn_ActiveEmployee, yn_PublishToWeb,
> yn_LockOut, yn_Remove, DateEntered, SortKey, ContactID, FirmID,
> LogProcess)
> SELECT
> Employee_ID, BusinessLocation, LastName, FirstName, NickName,
> WebPassword, Title, --Convert(varchar(5000),CurriculumVitae),
> ContactType, ContactOrder, Email, Phone, TollFree, Fax, WWW,
> yn_ActiveEmployee, yn_PublishToWeb,
> yn_LockOut, yn_Remove, DateEntered, SortKey, ContactID, FirmID,
> 'InsertUpdate'
> FROM inserted
>|||Scott,
The answer is yes to both questions, but I am not using the Employee_ID as
the PK and identity column in the log table:
CREATE TABLE [tblBusinessEmployee] (
[Employee_ID] [int] IDENTITY (1, 1) NOT NULL ,
CONSTRAINT [pk_BusinessEmployee] PRIMARY KEY CLUSTERED
(
[Employee_ID]
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
CREATE TABLE [tlogBusinessEmployee] (
[EmployeeLog_ID] [int] IDENTITY (1, 1) NOT NULL ,
[EmployeeID] [int] NULL ,
CONSTRAINT [pk_LogBusinessEmployee] PRIMARY KEY CLUSTERED
(
[EmployeeLog_ID]
) ON [PRIMARY]
) ON [PRIMARY]
INSERT into tlogBusinessEmployee
(EmployeeID, BusinessLocation, LastName, FirstName, NickName,
WebPassword, Title, --CurriculumVitae,
ContactType, ContactOrder, Email, Phone, TollFree, Fax, WWW,
yn_ActiveEmployee, yn_PublishToWeb,
yn_LockOut, yn_Remove, DateEntered, SortKey, ContactID, FirmID,
LogProcess)
SELECT
Employee_ID, BusinessLocation, LastName, FirstName, NickName,
WebPassword, Title, --Convert(varchar(5000),CurriculumVitae),
ContactType, ContactOrder, Email, Phone, TollFree, Fax, WWW,
yn_ActiveEmployee, yn_PublishToWeb,
yn_LockOut, yn_Remove, DateEntered, SortKey, ContactID, FirmID,
'InsertUpdate'
FROM inserted
"Scott Morris" <bogus@.bogus.com> wrote in message
news:e9BhBwLnEHA.1236@.TK2MSFTNGP09.phx.gbl...
> Sounds like a problem with the client code. Are you using an identity
> column as the PK for this table? Does the log table also have an identity
> column?
> "Steven K0" <stroy@.api.com> wrote in message
> news:efT2ywFnEHA.3392@.TK2MSFTNGP15.phx.gbl...
> > Hello, I am using the following trigger. After I insert a new record a
> new
> > record into the table, the record disappears and a previous record will
> show
> > up as the last record. If I change it to just an update trigger, it
works
> > fine. My question is why would an insert trigger cause this anomaly?|||As I indicated, the problem is in the client application. Most likely, the
application is using @.@.identity to identify the ID of the inserted employee
row, which would be incorrect in this case (this is supported by the remark
that the application works correctly when the insert trigger logic is
removed). use scope_identity() if using sql2k. Use of the profiler may help
to identify issues with the conversation between the client and the server.
"Steven K" <skaper@.troop.com> wrote in message
news:e352JZOnEHA.952@.TK2MSFTNGP10.phx.gbl...
> Scott,
> The answer is yes to both questions, but I am not using the Employee_ID as
> the PK and identity column in the log table:
> CREATE TABLE [tblBusinessEmployee] (
> [Employee_ID] [int] IDENTITY (1, 1) NOT NULL ,
> CONSTRAINT [pk_BusinessEmployee] PRIMARY KEY CLUSTERED
> (
> [Employee_ID]
> ) ON [PRIMARY]
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
>
> CREATE TABLE [tlogBusinessEmployee] (
> [EmployeeLog_ID] [int] IDENTITY (1, 1) NOT NULL ,
> [EmployeeID] [int] NULL ,
> CONSTRAINT [pk_LogBusinessEmployee] PRIMARY KEY CLUSTERED
> (
> [EmployeeLog_ID]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
>
>
> INSERT into tlogBusinessEmployee
> (EmployeeID, BusinessLocation, LastName, FirstName, NickName,
> WebPassword, Title, --CurriculumVitae,
> ContactType, ContactOrder, Email, Phone, TollFree, Fax, WWW,
> yn_ActiveEmployee, yn_PublishToWeb,
> yn_LockOut, yn_Remove, DateEntered, SortKey, ContactID, FirmID,
> LogProcess)
> SELECT
> Employee_ID, BusinessLocation, LastName, FirstName, NickName,
> WebPassword, Title, --Convert(varchar(5000),CurriculumVitae),
> ContactType, ContactOrder, Email, Phone, TollFree, Fax, WWW,
> yn_ActiveEmployee, yn_PublishToWeb,
> yn_LockOut, yn_Remove, DateEntered, SortKey, ContactID, FirmID,
> 'InsertUpdate'
> FROM inserted
>
> "Scott Morris" <bogus@.bogus.com> wrote in message
> news:e9BhBwLnEHA.1236@.TK2MSFTNGP09.phx.gbl...
> > Sounds like a problem with the client code. Are you using an identity
> > column as the PK for this table? Does the log table also have an
identity
> > column?
> >
> > "Steven K0" <stroy@.api.com> wrote in message
> > news:efT2ywFnEHA.3392@.TK2MSFTNGP15.phx.gbl...
> > > Hello, I am using the following trigger. After I insert a new record
a
> > new
> > > record into the table, the record disappears and a previous record
will
> > show
> > > up as the last record. If I change it to just an update trigger, it
> works
> > > fine. My question is why would an insert trigger cause this anomaly?
>

Insert Trigger

I am facing problem in creating a insert trigger for the following scenario.

i have transactions, control tables

whenever i insert a record in transactions it should get value from the control table, increment that value in control table and update the same value as transaction_id for new transaction in transaction table.

control table has these fields (control_desc, control_value)

can some one help me to write a trigger (insert trigger) in transactions table.

Tanks for the Help

Coudl you please provide more information like DDL code, showing which column you want to update etc.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks for the replay

Fields in Transactions Table : trans_id,trans_type,amount,trans_date,uid

Fields in Control Table: id, id_value

I am inserting all the fields in transction table except trans_id when insert trigger fires i want to take id_value from control table which id is "trans_id" in id coulmn update the same in the trans_id field in transactiontable.

Can u help me reating this trigger.

Regards

|||

Try this

--<Run Once>

drop table Transactions

drop table control

go

create table control

(

control_desc varchar(5) not null primary key,

control_value int not null

)

create table Transactions

(

trans_id int not null identity,

control_desc varchar(5) not null foreign key references control(control_desc),

control_value int not null

)

go

create trigger ti_Transactions on Transactions for insert

as

set nocount on

update c

set c.control_value = c.control_value + 1

from control c

join inserted i

on i.control_desc = c.control_desc

go

insert control select 'ABCDE', 10001

go

--</Run Once>

--<Repeatable>

insert transactions (control_desc, control_value)

select control_desc, control_value

from control

where control_desc = 'ABCDE'

go

select * from control

select * from transactions

--</Repeatable>

|||

The following query may help you...

Code Snippet

create table control (
id int,
id_value int)

Go

create table Transactions (
trans_id int,
trans_type int,
amount float,
trans_date datetime,
uid uniqueidentifier)

Go

Insert Into control values(1,0)--Initiating the value

Go

Create Trigger Trg_Insert_Transactions
On Transactions For Insert
As
Begin
SET NOCOUNT ON;
Declare @.Id as int;

Update control WITH (ROWLOCK)
Set
@.Id = Id_value = (Id_Value +1)
Where
id =1;
Update Transactions
Set
trans_id = @.Id
Where
uid = (Select Uid From Inserted)
End

Go

Insert Into Transactions values(null, 1, 10,getdate(),newid())

GO

select * from Transactions
select * from control