I want to create a basic insert trigger. In T1 I add a row, which creates
a
new ID. The trigger fires after the insert, how do I get the new ID to add
to the child table?
I know this is as simple as it gets but I've read about 10 posts and dont'
see it?
Thanks in advance.
Greg P.CREATE TRIGGER YourTriggerName
ON T1
FOR INSERT
AS
DECLARE @.newid int
IF @.@.ROWCOUNT=1
BEGIN
SET @.newid=(Select col1 FROM inserted)
--Do what you want do to with the @.newid here
END
Nathan H. Omukwenyi
"Greg P" <gsp@.newsgroups.nospam> wrote in message
news:872807BA-17FA-47A7-AAE1-AAF397488904@.microsoft.com...
>I want to create a basic insert trigger. In T1 I add a row, which creates
>a
> new ID. The trigger fires after the insert, how do I get the new ID to
> add
> to the child table?
> I know this is as simple as it gets but I've read about 10 posts and dont'
> see it?
> Thanks in advance.
> Greg P.|||try this...
create trigger trig1 on T1 after insert
as
begin
insert into [child table] ([new id])
select [new id] from inserted
end
"Greg P" wrote:
> I want to create a basic insert trigger. In T1 I add a row, which create
s a
> new ID. The trigger fires after the insert, how do I get the new ID to ad
d
> to the child table?
> I know this is as simple as it gets but I've read about 10 posts and dont'
> see it?
> Thanks in advance.
> Greg P.|||I read up on how to use the inserted table and this seems to answer the
question I posted, yet i have a bit of a different use than what was posted.
I need to look up values in two other tables before I can do my insert. I a
m
doing this with cursors. From what I am understanding I can't Declare
anything in a trigger, so to use the cursors I am calling a stored procedure
.
Inside this stored procedure is where I need to access the data in the
inserted table. Should I create a temp table and somehow copy the info from
the Inserted table into there?
FYI: I want to insert initail values for a 3 unique Id's into a 4th table.
So the inserted table contain the first ID's and I open cursors to store the
other 2 sets of ids. Then I am nesting the three cursors to insert a row fo
r
each of the three ID combinations.
IDCol1 IDCol2 IDCol 3 Tbl4Col
1 1 1 0
1 1 2 0
1 2 1 0
2 1 1 0
2 1 2 0
2 2 1 0
ect...
I hope that all makes sense. From what I know I can't do this in a trigger,
maye I can?
Thanks,
Greg
"Nathan H. Omukwenyi" wrote:
> CREATE TRIGGER YourTriggerName
> ON T1
> FOR INSERT
> AS
> DECLARE @.newid int
> IF @.@.ROWCOUNT=1
> BEGIN
> SET @.newid=(Select col1 FROM inserted)
> --Do what you want do to with the @.newid here
> END
>
> Nathan H. Omukwenyi
> "Greg P" <gsp@.newsgroups.nospam> wrote in message
> news:872807BA-17FA-47A7-AAE1-AAF397488904@.microsoft.com...
>
>|||Omni,
Any ideas on my new post?
Thanks,
Greg p
"Omnibuzz" wrote:
> try this...
> create trigger trig1 on T1 after insert
> as
> begin
> insert into [child table] ([new id])
> select [new id] from inserted
> end
> --
>
>
> "Greg P" wrote:
>|||>> want to create a basic insert trigger. In T1 I add a row, which creates a new ID
[sic]. The trigger fires after the insert, how do I get the new ID to add to the chil
d [sic] table? <<
Stop using SQL and go back to a network database. You have described
how they work as they build pointer chains as the data is inserted. I
am not kidding -- read a DB history book. You even used the term
"child" instead of "referenced" table!! Pure network/pointer chain
database concepts and terms, not anything like RDBMS.
Perhaps you should have read one book on RDBMS instead?
You do not create a relational key. It already exists in the real
world and you discover it.
Triggers are a kludge for putting procedural code into a declarative
language.
You need to start over; you do not know what you are doing. People
here will give you kludges to get rid of you quickly because we cannot
give you a 1-2 year course in RDBMS. Telling someone to "smash rats
with a rock when they get near your baby" is easier than "improve the
sewer system by learning civil engineering so rats are not a problem"
Look up this article: http://www.apa.org/journals/psp/psp7761121.html
Journal of Personality and Social Psychology
Unskilled and Unaware of It: How Difficulties in Recognizing One's Own
Incompetence Lead to Inflated Self-Assessments
Remember it takes SIX years to become a Union Journeyman Carpenter in
New York State. How many years to be an SQL programmer? A few w
s
in a ceritificate training class!|||On Tue, 9 May 2006 13:10:02 -0700, Greg P wrote:
>I read up on how to use the inserted table and this seems to answer the
>question I posted, yet i have a bit of a different use than what was posted
.
>I need to look up values in two other tables before I can do my insert. I
am
>doing this with cursors. From what I am understanding I can't Declare
>anything in a trigger, so to use the cursors I am calling a stored procedure.[/colo
r]
Hi Greg,
First misunderstanding: you CAN declare anything in a trigger. Whoever
told you otherwise obviously has little experience and even less
knowledge of SQL Server.
Second misunderstanding: Never ever use a cursor (*). And especially not
inside a trigger. Unless you want to ruin your performance and your
scalability, of course.
(*) Okay, there are SOME situations where a cursor is the best choice,
but they are very rare - only experienced DB programmers should be
allowed to use cursors, because it takes a lot of experience to
recognize a situation that might benefit from a cursor.
>Inside this stored procedure is where I need to access the data in the
>inserted table. Should I create a temp table and somehow copy the info fro
m
>the Inserted table into there?
If you MUST use the values from the inserted table in a stored
procedure, then yes, you must copy the data from inserted to some other
(preferably temporary) table.
But I don't think that this is the correct solution in your case.
>FYI: I want to insert initail values for a 3 unique Id's into a 4th table.
>So the inserted table contain the first ID's and I open cursors to store th
e
>other 2 sets of ids. Then I am nesting the three cursors to insert a row f
or
>each of the three ID combinations.
>IDCol1 IDCol2 IDCol 3 Tbl4Col
> 1 1 1 0
> 1 1 2 0
> 1 2 1 0
> 2 1 1 0
> 2 1 2 0
> 2 2 1 0
>ect...
>I hope that all makes sense.
To be blunt - not at all.
Please post the structure of all relevant tables, as CREATE TABLE
statements. Don't forget to include all constraints, properties and
indexes. Then, post some illustrative sample rows of data (as INSERT
statements), one or two sample INSERT statements that should fire the
trigger and the end results you need to have in your table after the
trigger has finished execution. With that information, we can probably
help you write this trigger without cursors or temp tables.
Hugo Kornelis, SQL Server MVP|||Hugo,
Thanks for the response. What I'm looking do is actually quite easily
explain. I'll use 4 tables and three relationsip, Widgets (WidegetsID PK),
Colors(ColorsIDPK), Sizes(SizesID PK) and WidgetsUsed (WidgetsUsedID,
WidgetsID FK, ColorsID Fk, )
I do an insert of multiple widgets creating multiple rows which are stored
in the "Insert" Table of the trigger. When a new Widget is inserted I need
to initialize the WidgetsUsed table. This means inserting a new record for
each size and color possibility and setting . (This table will need a row fo
r
each color and size that widget can come in)
If 2 Widgets were added A and B, and the colors are stored in the colors
table, and the sizes are stored in the sizes table. For this example lets
say there are three color in the color table and two sizes in the color
table. So for each Widget inserted into the widget table I want to add 6
records into the Widgets used table. Finally I'll call the field in the
table I'm updating MyDataField.
I know the idea of using curosr is bad now and they they are very
inefficient, but what I was thinking was I would use the cursors like old
adodb recordsets and loop through each one like the procedure below. I thin
k
this is a pretty thourough description of what I'm doing. Thanks for your
effort.
CREATE TABLE [dbo].[tblWidgetsUsed](
[WidgetUsedID] [uniqueidentifier] NULL,
[WidgetID] [uniqueidentifier] NULL,
[ColorID] [uniqueidentifier] NULL,
[SizeID] [uniqueidentifier] NULL,
[WidgetsOrdered] [numeric](18, 0) NULL
) ON [PRIMARY]
CREATE PROCEDURE dbo.spTrigUtilInsert
-- Add the parameters for the stored procedure here
@.WidgetID as int = 0,
@.ColorId as int = 0,
@.SizeID as int = 0
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Declare curWidget Cursor for
SELECT WidgetID FROM tblTemp --created from Insert in the trigger
open curWidget
fetch next from curWidget into @.WidgetID
--for each newly insertered record
while (@.@.Fetch_Status <> -1)
Declare curColor Cursor for SELECT ColorID FROM dbo.tblColor
open curColor
Fetch next from curColor into @.ColorID
-- For each Color
while (@.@.Fetch_Status <> -1)
-- Each Size
Declare curSize Cursor for SELECT DISTINCT SizeID FROM
dbo.tblSize
open curSize
fetch next from curSize into @.SizeID
While (@.@.Fetch_Status<>-1)
insert into tblWidgetsOrdered("WidgetID", "ColorID", "SizeID",
"NumOrdered")
Values (@.WidgetId, @.ColorID, @.SizeID, 0)
END
GO
Obviously these are not my real tables, because of security issues with my
project I can't use real tables but I think this show you what I'm looking t
o
do. I am using VS2005 Windows form (which is why I discuussed child and
parent tables, because I need to handle the insert order myself... I will
have a reply for the extreemly rude gent that is all high and mighty...)
Let me know what you think the way to do this is. I'm upgrading an access
based app to SQL Server 2005 and would like to take advantage of triggers to
initalize these rows. In the old applicaiton recordsets did the work.
Thanks again Hugo.
Greg P.
"Hugo Kornelis" wrote:
> On Tue, 9 May 2006 13:10:02 -0700, Greg P wrote:
>
> Hi Greg,
> First misunderstanding: you CAN declare anything in a trigger. Whoever
> told you otherwise obviously has little experience and even less
> knowledge of SQL Server.
> Second misunderstanding: Never ever use a cursor (*). And especially not
> inside a trigger. Unless you want to ruin your performance and your
> scalability, of course.
> (*) Okay, there are SOME situations where a cursor is the best choice,
> but they are very rare - only experienced DB programmers should be
> allowed to use cursors, because it takes a lot of experience to
> recognize a situation that might benefit from a cursor.
>
> If you MUST use the values from the inserted table in a stored
> procedure, then yes, you must copy the data from inserted to some other
> (preferably temporary) table.
> But I don't think that this is the correct solution in your case.
>
> To be blunt - not at all.
> Please post the structure of all relevant tables, as CREATE TABLE
> statements. Don't forget to include all constraints, properties and
> indexes. Then, post some illustrative sample rows of data (as INSERT
> statements), one or two sample INSERT statements that should fire the
> trigger and the end results you need to have in your table after the
> trigger has finished execution. With that information, we can probably
> help you write this trigger without cursors or temp tables.
> --
> Hugo Kornelis, SQL Server MVP
>|||For anyone else reading this please do not think I would ever speak this way
if it were not for the post this gentlemen made first.
Hey Genius,
Mr F&*%ing high and mighty... did you see the title of the post. I admitted
to being unfamiliar to using Triggers and Cursors and was looking for some
advice from this newsgroup. Your slam of a person who claims to be
unknowledgeable in topic shows absolute insecurity you informed prick. Yes
once again I will say you are more informed more than me about this, that’
s
why I’m asking the questions moron!!! You could explain nicely what issue
s
have yet I would have to charge you $150 an hour to be your psychologist
because I’m sure no one else want to talk to you and it still isn’t enou
gh
money to listen to your useless babble.
FYI, I have a degree in computer science and do understand RDBMS very
clearly. I have designed and implemented many solutions in many different
technologies. Now I’m learning to work with a new one, SQL Server 2005.
I know that when you update tables in VS2005 you need to handle the
add/mod/deletes yourself through typed datasets. These method must be calle
d
by hand and the terminology used for this process includes Parent, Child, an
d
Grandchild tables. Here is one link to such a reference in the updating
multiple tables section:
http://www.15seconds.com/issue/051123.htm
I also have a WROX’s Visual Basic 2005 Database Programming book in front
of
me, which is the “most advanced” book in this series which even has a di
agram
on page 173 discussing the use of Parent, Child and Grandchild insert, updat
e
and deletes.
So now I have to question what do you really know? It seems to me not much.
You can talk very loud and very rudely… yet not very intelligently. Pleas
e
do not lower the average IQ of my posts again with your “knowledge”.
Regards,
Greg P.
"--CELKO--" wrote:
> Stop using SQL and go back to a network database. You have described
> how they work as they build pointer chains as the data is inserted. I
> am not kidding -- read a DB history book. You even used the term
> "child" instead of "referenced" table!! Pure network/pointer chain
> database concepts and terms, not anything like RDBMS.
>
> Perhaps you should have read one book on RDBMS instead?
> You do not create a relational key. It already exists in the real
> world and you discover it.
> Triggers are a kludge for putting procedural code into a declarative
> language.
> You need to start over; you do not know what you are doing. People
> here will give you kludges to get rid of you quickly because we cannot
> give you a 1-2 year course in RDBMS. Telling someone to "smash rats
> with a rock when they get near your baby" is easier than "improve the
> sewer system by learning civil engineering so rats are not a problem"
> Look up this article: http://www.apa.org/journals/psp/psp7761121.html
> Journal of Personality and Social Psychology
> Unskilled and Unaware of It: How Difficulties in Recognizing One's Own
> Incompetence Lead to Inflated Self-Assessments
> Remember it takes SIX years to become a Union Journeyman Carpenter in
> New York State. How many years to be an SQL programmer? A few w
s
> in a ceritificate training class!
>|||Greg,
I dunno if this is what you want. And sorry for the delayed reply.. And
try to decipher this because I just got up from bed :)
try this...
create trigger trig1 on Widgets after insert
as
begin
insert into WidgetsUsed(WidgetID, ColorID, SizeID, NumOrdered)
select
a.WidegetsID,
b.colorsID,
c.sizesID,
0
from inserted a,
colors B,
sizes c
end
Let me know if this was what you wanted.
mins can use the extended procedure