insert, update issue - stored procedure workaround

any stored procedure guru's around ?

I'm going nuts around here.
ok basically I've create a multilangual website using global en localresources for the static parts and a DB for the dynamic part.
I'm using the PROFILE option in asp.net 2.0 to store the language preference of visitors. It's working perfectly.

but Now I have some problems trying to get the right inserts.

basically I have designed my db based on this article:
http://www.codeproject.com/aspnet/LocalizedSamplePart2.asp?print=true

more specifically:
http://www.codeproject.com/aspnet/LocalizedSamplePart2/normalizedSchema.gif

ok now let's take the example of Categorie, Categorie_Local, and Culture

I basically want to create an insert that will let me insert categories into my database with the 2 language:

eg.
in categorie I have ID's 1 & 2
in culture I have:
ID: 1
culture: en-US
ID 2
culture: fr-Be

now the insert should create into Categorie_Local:

cat_id culture_id name
1 1 a category
1 2 une categorie

and so on...

I think this thing is only do-able with a stored procedure because:

1. when creating a new categorie, a new ID has to be entered into Categorie table
2. into the Categorie_local I need 2 rows inserted with the 2 values for 2 different cultures...

any idea on how to do this right ?
I'm a newbie with ms sql and stored procedures :s

help would be very very appreciated!
thanks a lotOK I got it ;)

it's not the best procedure out there I guess since I'm statically assigning my culture_id's but in this case 2 language are more than enough ;)

1 CREATEPROCEDURE [dbo].[InsCategories]2-- Add the parameters for the stored procedure here3@.cat_naam_envarchar(200),4@.cat_naam_nlvarchar(200),5@.cat_dateDateTime6AS7SET NOCOUNT ON;8BEGIN TRAN AddCategory9DECLARE @.cat_idint10Insert into Categorie11(Cat_date)12VALUES (@.cat_date)1314SELECT15@.cat_id=@.@.Identity1617--static: culture_id=1 for dutch18--static: culture_id=2 for english19Insert Into Categorie_Local20(cat_id, culture_id, catnaam)21VALUES (@.cat_id,1,@.cat_naam_nl)2223Insert Into Categorie_Local24(cat_id, culture_id, catnaam)25VALUES (@.cat_id,2,@.cat_naam_en)2627COMMIT Tran AddCategory28

sql

insert trigger

I need to add a trigger to a table to fire when an insert event happens.
Basically I need to update 2 columns (employee last name and first) from
another table. Something along these lines:
UPDATE dbo.test
SET dbo.test.lastname = dbo.employee.lastname,
dbo.test.firstname = dbo.employee.firstname
FROM dbo.test, dbo.employee
WHERE dbo.test.logonid = dbo.employee.account
any help is appreciated.Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.server:426824
On Thu, 9 Mar 2006 12:00:17 -0800, Shane Faullin wrote:

>I need to add a trigger to a table to fire when an insert event happens.
>Basically I need to update 2 columns (employee last name and first) from
>another table. Something along these lines:
>UPDATE dbo.test
>SET dbo.test.lastname = dbo.employee.lastname,
>dbo.test.firstname = dbo.employee.firstname
>FROM dbo.test, dbo.employee
>WHERE dbo.test.logonid = dbo.employee.account
>any help is appreciated.
Hi Shane,
I'm not sure if I understand your requirements. It appears that you want
a trigger to copy information that is inserted into one table over to
another table. That is sually not a good idea: storing redundant data
wastes disk space, and (much more important!) introduces the risk of
getting data corruption - what if the two copies of the data are someday
not equal? Which of the two conflicting data sources should be
considered the "correct" source? And if source A is considered "correct"
in case of a conflict, what's the point of having source B?
But I might be wrong. Maybe I'm misunderstanding what you're trying to
do, or why you're trying to do it. If that's the case, then please write
back with the structure of your tables (posted as CREATE TABLE
statements, including all constaints and properties - though you may
omit irrelevant columns), some rows of sample starting data (posted as
INSERT statements), some typical INSERT statements that should fire the
trigger and the end result you expect after executing those statements
(i.e. the end result that the trigger should generate).
See www.aspfaq.com.5006 for more info on how to assemble the required
information.
Hugo Kornelis, SQL Server MVP|||essentially what would happen is a when a row was inserted into a table A, I
need a trigger to use the value in the employeeid column to locate the
employee record (based upon the employeeid) containing the lastname &
firstname columnsin table B and update the lastname and firstname columns i
n
the same row in table A.
"Hugo Kornelis" wrote:

> On Thu, 9 Mar 2006 12:00:17 -0800, Shane Faullin wrote:
>
> Hi Shane,
> I'm not sure if I understand your requirements. It appears that you want
> a trigger to copy information that is inserted into one table over to
> another table. That is sually not a good idea: storing redundant data
> wastes disk space, and (much more important!) introduces the risk of
> getting data corruption - what if the two copies of the data are someday
> not equal? Which of the two conflicting data sources should be
> considered the "correct" source? And if source A is considered "correct"
> in case of a conflict, what's the point of having source B?
> But I might be wrong. Maybe I'm misunderstanding what you're trying to
> do, or why you're trying to do it. If that's the case, then please write
> back with the structure of your tables (posted as CREATE TABLE
> statements, including all constaints and properties - though you may
> omit irrelevant columns), some rows of sample starting data (posted as
> INSERT statements), some typical INSERT statements that should fire the
> trigger and the end result you expect after executing those statements
> (i.e. the end result that the trigger should generate).
> See www.aspfaq.com.5006 for more info on how to assemble the required
> information.
> --
> Hugo Kornelis, SQL Server MVP
>|||On Sun, 12 Mar 2006 14:13:27 -0800, Shane Faullin wrote:

>essentially what would happen is a when a row was inserted into a table A,
I
>need a trigger to use the value in the employeeid column to locate the
>employee record (based upon the employeeid) containing the lastname &
>firstname columnsin table B and update the lastname and firstname columns
in
>the same row in table A.
Hi Shane,
I see. So you want a default, but more complex than a standard DEFAULT
property has to offer.
I think you still should consider if you really need to redundantly
store the firstname and lastname in both tables. But here's a quick
attempt at the code:
CREATE TRIGGER MyTrigger
ON TableA
INSTEAD OF INSERT
AS
INSERT INTO TableA (EmployeeID, OtherColumns, FirstName, LastName)
SELECT i.EmployeeID, i.OtherColumns, e.FirstName, e.LastName
FROM inserted AS i
LEFT OUTER JOIN Employees AS e
ON e.EmployeeID = i.EmployeeID
go
Note that there's no error handling included. Also note that this code
is untested - see www.aspfaq.com.5006 if you prefer a tested reply.
Hugo Kornelis, SQL Server MVP|||Shane,
CREATE TRIGGER ON dbo.test
FOR INSERT
AS
SET NOCOUNT ON
UPDATE t
SET
t.lastname = e.lastname,
t.firstname = e.firstname
FROM dbo.test t, inserted i, dbo.employee e
WHERE t.logonid = i.logonid /* or whatever dbo.test's primary key is */
AND t.logonid = e.account
SET NOCOUNT OFF
GO
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"Shane Faullin" <sfaullin@.jupitermed.com.jupiterflorida> wrote in message
news:A7952F6F-A5F0-4CDB-A6CD-B44A46267A77@.microsoft.com...
>I need to add a trigger to a table to fire when an insert event happens.
> Basically I need to update 2 columns (employee last name and first) from
> another table. Something along these lines:
> UPDATE dbo.test
> SET dbo.test.lastname = dbo.employee.lastname,
> dbo.test.firstname = dbo.employee.firstname
> FROM dbo.test, dbo.employee
> WHERE dbo.test.logonid = dbo.employee.account
> any help is appreciated.|||You can do a SELECT on the inserted virtual table.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:D6F26A0E-9667-4EC9-980E-26F935443749@.microsoft.com...
Hello,
My table has a uniqueidentifier with default value newid() and I need to
catch newly created uniqueidentifier in my insert trigger, how can I do
this?|||You can do a SELECT on the inserted virtual table.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:D6F26A0E-9667-4EC9-980E-26F935443749@.microsoft.com...
Hello,
My table has a uniqueidentifier with default value newid() and I need to
catch newly created uniqueidentifier in my insert trigger, how can I do
this?

insert trigger

I need to add a trigger to a table to fire when an insert event happens.
Basically I need to update 2 columns (employee last name and first) from
another table. Something along these lines:
UPDATE dbo.test
SET dbo.test.lastname = dbo.employee.lastname,
dbo.test.firstname = dbo.employee.firstname
FROM dbo.test, dbo.employee
WHERE dbo.test.logonid = dbo.employee.account
any help is appreciated.
Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.server:426824
On Thu, 9 Mar 2006 12:00:17 -0800, Shane Faullin wrote:

>I need to add a trigger to a table to fire when an insert event happens.
>Basically I need to update 2 columns (employee last name and first) from
>another table. Something along these lines:
>UPDATE dbo.test
>SET dbo.test.lastname = dbo.employee.lastname,
>dbo.test.firstname = dbo.employee.firstname
>FROM dbo.test, dbo.employee
>WHERE dbo.test.logonid = dbo.employee.account
>any help is appreciated.
Hi Shane,
I'm not sure if I understand your requirements. It appears that you want
a trigger to copy information that is inserted into one table over to
another table. That is sually not a good idea: storing redundant data
wastes disk space, and (much more important!) introduces the risk of
getting data corruption - what if the two copies of the data are someday
not equal? Which of the two conflicting data sources should be
considered the "correct" source? And if source A is considered "correct"
in case of a conflict, what's the point of having source B?
But I might be wrong. Maybe I'm misunderstanding what you're trying to
do, or why you're trying to do it. If that's the case, then please write
back with the structure of your tables (posted as CREATE TABLE
statements, including all constaints and properties - though you may
omit irrelevant columns), some rows of sample starting data (posted as
INSERT statements), some typical INSERT statements that should fire the
trigger and the end result you expect after executing those statements
(i.e. the end result that the trigger should generate).
See www.aspfaq.com.5006 for more info on how to assemble the required
information.
Hugo Kornelis, SQL Server MVP
|||essentially what would happen is a when a row was inserted into a table A, I
need a trigger to use the value in the employeeid column to locate the
employee record (based upon the employeeid) containing the lastname &
firstname columnsin table B and update the lastname and firstname columns in
the same row in table A.
"Hugo Kornelis" wrote:

> On Thu, 9 Mar 2006 12:00:17 -0800, Shane Faullin wrote:
>
> Hi Shane,
> I'm not sure if I understand your requirements. It appears that you want
> a trigger to copy information that is inserted into one table over to
> another table. That is sually not a good idea: storing redundant data
> wastes disk space, and (much more important!) introduces the risk of
> getting data corruption - what if the two copies of the data are someday
> not equal? Which of the two conflicting data sources should be
> considered the "correct" source? And if source A is considered "correct"
> in case of a conflict, what's the point of having source B?
> But I might be wrong. Maybe I'm misunderstanding what you're trying to
> do, or why you're trying to do it. If that's the case, then please write
> back with the structure of your tables (posted as CREATE TABLE
> statements, including all constaints and properties - though you may
> omit irrelevant columns), some rows of sample starting data (posted as
> INSERT statements), some typical INSERT statements that should fire the
> trigger and the end result you expect after executing those statements
> (i.e. the end result that the trigger should generate).
> See www.aspfaq.com.5006 for more info on how to assemble the required
> information.
> --
> Hugo Kornelis, SQL Server MVP
>
|||On Sun, 12 Mar 2006 14:13:27 -0800, Shane Faullin wrote:

>essentially what would happen is a when a row was inserted into a table A, I
>need a trigger to use the value in the employeeid column to locate the
>employee record (based upon the employeeid) containing the lastname &
>firstname columnsin table B and update the lastname and firstname columns in
>the same row in table A.
Hi Shane,
I see. So you want a default, but more complex than a standard DEFAULT
property has to offer.
I think you still should consider if you really need to redundantly
store the firstname and lastname in both tables. But here's a quick
attempt at the code:
CREATE TRIGGER MyTrigger
ON TableA
INSTEAD OF INSERT
AS
INSERT INTO TableA (EmployeeID, OtherColumns, FirstName, LastName)
SELECT i.EmployeeID, i.OtherColumns, e.FirstName, e.LastName
FROM inserted AS i
LEFT OUTER JOIN Employees AS e
ON e.EmployeeID = i.EmployeeID
go
Note that there's no error handling included. Also note that this code
is untested - see www.aspfaq.com.5006 if you prefer a tested reply.
Hugo Kornelis, SQL Server MVP
|||Shane,
CREATE TRIGGER ON dbo.test
FOR INSERT
AS
SET NOCOUNT ON
UPDATE t
SET
t.lastname = e.lastname,
t.firstname = e.firstname
FROM dbo.test t, inserted i, dbo.employee e
WHERE t.logonid = i.logonid /* or whatever dbo.test's primary key is */
AND t.logonid = e.account
SET NOCOUNT OFF
GO
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"Shane Faullin" <sfaullin@.jupitermed.com.jupiterflorida> wrote in message
news:A7952F6F-A5F0-4CDB-A6CD-B44A46267A77@.microsoft.com...
>I need to add a trigger to a table to fire when an insert event happens.
> Basically I need to update 2 columns (employee last name and first) from
> another table. Something along these lines:
> UPDATE dbo.test
> SET dbo.test.lastname = dbo.employee.lastname,
> dbo.test.firstname = dbo.employee.firstname
> FROM dbo.test, dbo.employee
> WHERE dbo.test.logonid = dbo.employee.account
> any help is appreciated.

insert trigger

I need to add a trigger to a table to fire when an insert event happens.
Basically I need to update 2 columns (employee last name and first) from
another table. Something along these lines:
UPDATE dbo.test
SET dbo.test.lastname = dbo.employee.lastname,
dbo.test.firstname = dbo.employee.firstname
FROM dbo.test, dbo.employee
WHERE dbo.test.logonid = dbo.employee.account
any help is appreciated.On Thu, 9 Mar 2006 12:00:17 -0800, Shane Faullin wrote:
>I need to add a trigger to a table to fire when an insert event happens.
>Basically I need to update 2 columns (employee last name and first) from
>another table. Something along these lines:
>UPDATE dbo.test
>SET dbo.test.lastname = dbo.employee.lastname,
>dbo.test.firstname = dbo.employee.firstname
>FROM dbo.test, dbo.employee
>WHERE dbo.test.logonid = dbo.employee.account
>any help is appreciated.
Hi Shane,
I'm not sure if I understand your requirements. It appears that you want
a trigger to copy information that is inserted into one table over to
another table. That is sually not a good idea: storing redundant data
wastes disk space, and (much more important!) introduces the risk of
getting data corruption - what if the two copies of the data are someday
not equal? Which of the two conflicting data sources should be
considered the "correct" source? And if source A is considered "correct"
in case of a conflict, what's the point of having source B?
But I might be wrong. Maybe I'm misunderstanding what you're trying to
do, or why you're trying to do it. If that's the case, then please write
back with the structure of your tables (posted as CREATE TABLE
statements, including all constaints and properties - though you may
omit irrelevant columns), some rows of sample starting data (posted as
INSERT statements), some typical INSERT statements that should fire the
trigger and the end result you expect after executing those statements
(i.e. the end result that the trigger should generate).
See www.aspfaq.com.5006 for more info on how to assemble the required
information.
--
Hugo Kornelis, SQL Server MVP|||essentially what would happen is a when a row was inserted into a table A, I
need a trigger to use the value in the employeeid column to locate the
employee record (based upon the employeeid) containing the lastname &
firstname columnsin table B and update the lastname and firstname columns in
the same row in table A.
"Hugo Kornelis" wrote:
> On Thu, 9 Mar 2006 12:00:17 -0800, Shane Faullin wrote:
> >I need to add a trigger to a table to fire when an insert event happens.
> >Basically I need to update 2 columns (employee last name and first) from
> >another table. Something along these lines:
> >
> >UPDATE dbo.test
> >SET dbo.test.lastname = dbo.employee.lastname,
> >dbo.test.firstname = dbo.employee.firstname
> >FROM dbo.test, dbo.employee
> >WHERE dbo.test.logonid = dbo.employee.account
> >
> >any help is appreciated.
> Hi Shane,
> I'm not sure if I understand your requirements. It appears that you want
> a trigger to copy information that is inserted into one table over to
> another table. That is sually not a good idea: storing redundant data
> wastes disk space, and (much more important!) introduces the risk of
> getting data corruption - what if the two copies of the data are someday
> not equal? Which of the two conflicting data sources should be
> considered the "correct" source? And if source A is considered "correct"
> in case of a conflict, what's the point of having source B?
> But I might be wrong. Maybe I'm misunderstanding what you're trying to
> do, or why you're trying to do it. If that's the case, then please write
> back with the structure of your tables (posted as CREATE TABLE
> statements, including all constaints and properties - though you may
> omit irrelevant columns), some rows of sample starting data (posted as
> INSERT statements), some typical INSERT statements that should fire the
> trigger and the end result you expect after executing those statements
> (i.e. the end result that the trigger should generate).
> See www.aspfaq.com.5006 for more info on how to assemble the required
> information.
> --
> Hugo Kornelis, SQL Server MVP
>|||On Sun, 12 Mar 2006 14:13:27 -0800, Shane Faullin wrote:
>essentially what would happen is a when a row was inserted into a table A, I
>need a trigger to use the value in the employeeid column to locate the
>employee record (based upon the employeeid) containing the lastname &
>firstname columnsin table B and update the lastname and firstname columns in
>the same row in table A.
Hi Shane,
I see. So you want a default, but more complex than a standard DEFAULT
property has to offer.
I think you still should consider if you really need to redundantly
store the firstname and lastname in both tables. But here's a quick
attempt at the code:
CREATE TRIGGER MyTrigger
ON TableA
INSTEAD OF INSERT
AS
INSERT INTO TableA (EmployeeID, OtherColumns, FirstName, LastName)
SELECT i.EmployeeID, i.OtherColumns, e.FirstName, e.LastName
FROM inserted AS i
LEFT OUTER JOIN Employees AS e
ON e.EmployeeID = i.EmployeeID
go
Note that there's no error handling included. Also note that this code
is untested - see www.aspfaq.com.5006 if you prefer a tested reply.
--
Hugo Kornelis, SQL Server MVP|||Shane,
CREATE TRIGGER ON dbo.test
FOR INSERT
AS
SET NOCOUNT ON
UPDATE t
SET
t.lastname = e.lastname,
t.firstname = e.firstname
FROM dbo.test t, inserted i, dbo.employee e
WHERE t.logonid = i.logonid /* or whatever dbo.test's primary key is */
AND t.logonid = e.account
SET NOCOUNT OFF
GO
--
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"Shane Faullin" <sfaullin@.jupitermed.com.jupiterflorida> wrote in message
news:A7952F6F-A5F0-4CDB-A6CD-B44A46267A77@.microsoft.com...
>I need to add a trigger to a table to fire when an insert event happens.
> Basically I need to update 2 columns (employee last name and first) from
> another table. Something along these lines:
> UPDATE dbo.test
> SET dbo.test.lastname = dbo.employee.lastname,
> dbo.test.firstname = dbo.employee.firstname
> FROM dbo.test, dbo.employee
> WHERE dbo.test.logonid = dbo.employee.account
> any help is appreciated.

insert then update...

greetings
I am developing an application for the marketing dept at my company.Basically users can build the content of an email to be sent to oursubscriber database.
I am wanting the application to initailly save the content into a database, the update the most recently inserted row.
The save button uses the following SQL command:
Dim SqlMethod As String ="INSERT INTO CZC_email (Offer, SendDate, Destinations, Copy, BannerURL)VALUES ('" & txtCampaignName.Text & "','" &calCampaignDate.SelectedDate.ToString("yy/dd/MM") & "','" &DestinationsSelected & "','" & FreeTextBox2.Text & "', '"& txtBannerPath.Text & "')SELECT @.@.IDENTITY AS 'CZ_ID'"
And my update button has this SQL command:
Dim SqlMethod As String ="UPDATE CZC_email SET SendDate = '" &calCampaignDate.SelectedDate.ToString("yy/dd/MM") & "', Offer = '"& txtCampaignName.Text & "',Destinations = '" &DestinationsSelected & "', BannerURL = '" & txtBannerPath.Text& "' WHERE CZ_ID = @.@.IDENTITY "
but it doesnt seem to be updating. anyone know what I'm doing wrong?
Cheers

(1) Use a stored proc.
(2) Use SCOPE_IDENTITY() instead of @.@.IDENTIY. Check books on line for the differences.
(3) Use Parameterized Queries to prevent SQL Injection atatcks (google for more info on this).|||Save the @.@.IDENTITY values you get from insert command & then in the update command pass the value returned from the insertion instead of @.@.IDENTITY|||I've tried saving the @.@.identity and scope_identity as a value ofvariable varCZ_ID by using the following code (i'm using the MS DAAB)
varCZ_ID = dataReader("SCOPE_IDENTITY")
or

varCZ_ID = dataReader("CZ_ID")

however, this is erroring 'Invalid attempt to read when no data is present.'
Any ideas what I'm doing wrong? this is really doing my head in!
Thanks
|||If you use a stored proc you could save a trip to the server and get back accurate Id.
|||yes I agree, I just wanted to get it working first off.
I managed to fix the problem by saving the @.@.IDENTITY into a session variable.

|||@.@.IDENTITY does not always give you the identity value that just gotgenerated by the insert statement. If multiple calls were made at thesame time it could mix up the Id's. So it is advised to useSCOPE_DENTITY() instead of @.@.IDENTITY.
|||Thats a good point, and something I am aware off.
For this particular application is not a big deal, as it only going to be used by two people - and not at the sametime.
However i will look to improve it soon, and that will be things I will do.
Cheers

Insert Problem in MS SQL

hey, I have written a bunch of insert statements and basically all I am trying to do is fill up columns of one table with values from columns of the second table.

My insert statement looks like:

INSERT table1(column)
SELECT column
FROM table2

Now, this works for one column , but when I run another statement like this trying to update another column, the second time around it does not work.
It does not error out, it shows that it runs fine, but the data is not shown on the table. Some of the data which is shown removes the data from the first column in the adjacent row. I am sure I am missing something here, but not able to figure it out, please HELP .Does your code look like this.

INSERT INTO Table1
( Col1, Col2, Col3....Etc)
SELECT col1, Col2, Col3
FROM Table2

If not then it won't work. Post your code and I'll have a look

Cheers
C|||Hi!
I think you have to do some change.you may use following codes,else email me your total code .Than i heartly solve thats....
INSERT INTO Table1
( Col1, Col2, Col3....Etc)
VALUES(SELECT col1, Col2, Col3....etc
FROM Table2)

Ok...bye