ok i know the rows being affected are put in these temporary tables, but
if i do an insert with 5 rows does inserted have 5 rows in it?
if htats the case how do you check field values for every row
I was using
if (select newfield from #inserted) = this
begin
update #inserted set newfield = that
end
but that isnt going to work if inserted contains all 5 rows, i thought
inserted only had the current row and it passed through the instead of
trigger 5 times once for each row. if thats not the case how do you do
something like
for each newfield in #inserted do
if newfield is this
set it to this.
for example
say i have an insert with three fields
category, categoryid, name
and the 3 rows in my insert are
('Standard', 1, 'Toys')
('NonStandard, null, 'Games')
('Misc', null, 'Puzzles')
and in my instead of trigger i want to fill the nulls with the proper
number so in my instead of trigger i say
if (field2 is null)
begin
set field2 = (select rightnumber from mastertable where name = field2)
end
but it has to do it for each rowChris M wrote:
> ok i know the rows being affected are put in these temporary tables,
> but if i do an insert with 5 rows does inserted have 5 rows in it?
> if htats the case how do you check field values for every row
> I was using
> if (select newfield from #inserted) = this
> begin
> update #inserted set newfield = that
> end
> but that isnt going to work if inserted contains all 5 rows, i thought
> inserted only had the current row and it passed through the instead of
> trigger 5 times once for each row. if thats not the case how do you
> do something like
> for each newfield in #inserted do
> if newfield is this
> set it to this.
> for example
> say i have an insert with three fields
> category, categoryid, name
> and the 3 rows in my insert are
> ('Standard', 1, 'Toys')
> ('NonStandard, null, 'Games')
> ('Misc', null, 'Puzzles')
> and in my instead of trigger i want to fill the nulls with the proper
> number so in my instead of trigger i say
> if (field2 is null)
> begin
> set field2 = (select rightnumber from mastertable where name = field2)
> end
> but it has to do it for each row
Yes. The inserted and deleted logical tables contain all affected rows.
No. You cannot modify data in the inserted and deleted tables, so I'm
not quite sure how your code was even executing. The tables do not have
a '#' prefix. The are plainly 'inserted' and 'deleted'.
How would you get the "right number" from mastertable is the second
column is NULL. What are you joining on?
Personally, I would just throw up a RAISERROR. I don't really understand
your test scenario. If you could join up with mastertable, then it seems
you should be using a FK to that table rather than repeating data
values.
Could you provide the DDL for the tables in question.
David Gugick
Imceda Software
www.imceda.com|||It will do it for each row in inserted, just write the expression on the
right of the set newfield =...
so that it will be a different value for each row of inserted...
update Table set newfield =
Case newfield
When 'this' Then 'That'
When 'TheOther' Then 'OtherThat'
End
But what is #Inserted? a Temporary Table?
What are you trying to update in this trigger?
"Chris M" wrote:
> ok i know the rows being affected are put in these temporary tables, but
> if i do an insert with 5 rows does inserted have 5 rows in it?
> if htats the case how do you check field values for every row
> I was using
> if (select newfield from #inserted) = this
> begin
> update #inserted set newfield = that
> end
> but that isnt going to work if inserted contains all 5 rows, i thought
> inserted only had the current row and it passed through the instead of
> trigger 5 times once for each row. if thats not the case how do you do
> something like
> for each newfield in #inserted do
> if newfield is this
> set it to this.
> for example
> say i have an insert with three fields
> category, categoryid, name
> and the 3 rows in my insert are
> ('Standard', 1, 'Toys')
> ('NonStandard, null, 'Games')
> ('Misc', null, 'Puzzles')
> and in my instead of trigger i want to fill the nulls with the proper
> number so in my instead of trigger i say
> if (field2 is null)
> begin
> set field2 = (select rightnumber from mastertable where name = field2)
> end
> but it has to do it for each row
>|||--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
Stop thinking procedure and start thinking sets. If there are 3 rows in
the inserted set and 2 have NULL in some column (as your example shows)
you can do an UPDATE like this:
UPDATE original_table
SET column_name = (select rightnumber
from mastertable m inner join inserted i
on m.<join cols> = i.<join cols> )
WHERE EXISTS (SELECT * FROM inserted
WHERE column_name IS NULL
AND inserted.ID = original_table.ID)
The "WHERE column_name IS NULL" in the UPDATE's WHERE clause subquery
will identify the rows in original_table that have the 'column_name' set
to the "rightnumber."
The <join cols> have to be a column, or columns, that uniquely identify
the rows in inserted that relate to rows in mastertable, so the
"rightnumber" can be retrieved. I would have to see the design of
mastertable and original_table to determine which columns those would
be. You could even do w/o the inserted set and just use something in
the mastertable that identifies which row in mastertable has the correct
data that is to be placed in the original_table. IOW, if you had a
Default value in mastertable that always goes in that column - data in
mastertable looks like this:
column_ rightnumber
-- --
Price 25
The SET subquery would look like this:
SET Price = (select rightnumber
from mastertable
where column_ = 'Price')
NB: By now you should realize that you can create a DEFAULT on the
column(s) in original_table instead of using a trigger like the above.
E.g.: CREATE TABLE T (col_1 int, col_a char(2) default ('zz'))
insert into t (col_1) values (2)
select * from t
col_1 col_a
-- --
2 zz
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQmguqYechKqOuFEgEQJJSgCg8iAqLWvq7TwF
9BLQlhBbcY/uxx0AnRRi
DXmdayzhLIxU5WBk4wSL4x4R
=mDFy
--END PGP SIGNATURE--
Chris M wrote:
> ok i know the rows being affected are put in these temporary tables, but
> if i do an insert with 5 rows does inserted have 5 rows in it?
> if htats the case how do you check field values for every row
> I was using
> if (select newfield from #inserted) = this
> begin
> update #inserted set newfield = that
> end
> but that isnt going to work if inserted contains all 5 rows, i thought
> inserted only had the current row and it passed through the instead of
> trigger 5 times once for each row. if thats not the case how do you do
> something like
> for each newfield in #inserted do
> if newfield is this
> set it to this.
> for example
> say i have an insert with three fields
> category, categoryid, name
> and the 3 rows in my insert are
> ('Standard', 1, 'Toys')
> ('NonStandard, null, 'Games')
> ('Misc', null, 'Puzzles')
> and in my instead of trigger i want to fill the nulls with the proper
> number so in my instead of trigger i say
> if (field2 is null)
> begin
> set field2 = (select rightnumber from mastertable where name = field2)
> end
> but it has to do it for each row
>|||MGFoster wrote:
> --BEGIN PGP SIGNED MESSAGE--
> Hash: SHA1
> Stop thinking procedure and start thinking sets. If there are 3 rows in
> the inserted set and 2 have NULL in some column (as your example shows)
> you can do an UPDATE like this:
> UPDATE original_table
> SET column_name = (select rightnumber
> from mastertable m inner join inserted i
> on m.<join cols> = i.<join cols> )
> WHERE EXISTS (SELECT * FROM inserted
> WHERE column_name IS NULL
> AND inserted.ID = original_table.ID)
> The "WHERE column_name IS NULL" in the UPDATE's WHERE clause subquery
> will identify the rows in original_table that have the 'column_name' set
> to the "rightnumber."
> The <join cols> have to be a column, or columns, that uniquely identify
> the rows in inserted that relate to rows in mastertable, so the
> "rightnumber" can be retrieved. I would have to see the design of
> mastertable and original_table to determine which columns those would
> be. You could even do w/o the inserted set and just use something in
> the mastertable that identifies which row in mastertable has the correct
> data that is to be placed in the original_table. IOW, if you had a
> Default value in mastertable that always goes in that column - data in
> mastertable looks like this:
> column_ rightnumber
> -- --
> Price 25
> The SET subquery would look like this:
> SET Price = (select rightnumber
> from mastertable
> where column_ = 'Price')
> NB: By now you should realize that you can create a DEFAULT on the
> column(s) in original_table instead of using a trigger like the above.
> E.g.: CREATE TABLE T (col_1 int, col_a char(2) default ('zz'))
> insert into t (col_1) values (2)
> select * from t
> col_1 col_a
> -- --
> 2 zz
I can do that sometimes but if i want to set something like an id number
based on a column in another table that i cannot join on i can't do it
with a set
unless there is a command like
select * into #inserted from inserted
update #inserted
set keyfield = getNextValueFromTable()
insert into myTable select * from #inserted
but I cannot figure out how to get the getNextValueFromTable()
procedure since i cannot call stored procedures that way and UDF's
cannot access my table values
I am only doing this the way i'm doing it to maintain compatibility with
a program. If i was to design this myself I'd be doing it with
constraints, foreign keys, identities, etc|||Chris M wrote:
> MGFoster wrote:
>
< SNIP >
> I can do that sometimes[,] but if i want to set something like an id numbe
r
> based on a column in another table that i cannot join on i can't do it
> with a set
>
<SNIP >
--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
How can you know which "id number[...]in another table" to use if you
cannot join on it? That implies that there is "some other" way of
determining the relationship between one table and another; and, that
that relationship is defined outside the database. This goes against
RDB design principles.
If the "id number [is] based on a column in another table" that means
there is a relationship between the 2 tables. If there is a
relationship between the 2 tables you can join them.
So, what's going on there? ;-)
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQmgyZYechKqOuFEgEQJd5QCfaDO4xXjYNt2S
oYbvwG9acyT4ncAAoPyO
BL03rAJrHegoe1ktC8L/pRBI
=8GJu
--END PGP SIGNATURE--|||What do you mean..
<snip> ...that i cannot join on ...</snip>
Why Not?
If the objective here is to insert some records into MyTable, then just do
that in the trigger
Insert MyTable
Select <Stuff>
From inserted
The <Stuff> above needs t oeb written as a set-based expression, (Set of
expressions), such that the values will be appropriate... But there's no way
for us to guess what that is until you tell us whjat you are trying to do
with getNextValueFromTable()...
again, if all you are tyrying to do is set the value based on the value in
the inserted table, then, as an example...
Insert MyTable
Select <OtherColumns>,
Case newField
When <ValueA> Then <outValueA>
When <ValueB> Then <outValueB>
When <ValueC> Then <outValueC>
When <ValueD> Then <outValueD>
Else <OutVAlueDefault> End
From inserted|||MGFoster wrote:
> Chris M wrote:
>
> < SNIP >
>
> <SNIP >
> --BEGIN PGP SIGNED MESSAGE--
> Hash: SHA1
> How can you know which "id number[...]in another table" to use if you
> cannot join on it? That implies that there is "some other" way of
> determining the relationship between one table and another; and, that
> that relationship is defined outside the database. This goes against
> RDB design principles.
> If the "id number [is] based on a column in another table" that means
> there is a relationship between the 2 tables. If there is a
> relationship between the 2 tables you can join them.
> So, what's going on there? ;-)
A table called generators
create table generators (
generator_name varchar(50),
generator_lastid integer
)
in my table say myTable if i want to get the next ID from generators i
have to
select generator_lastid from generators where generator_name =
'gen_id_mytable)
so i do not know how i can join on that, and I'm sure this does violate
some rule, but its meant to simulate the sequence/generator object of
oracle/interbase/firebird|||> if i do an insert with 5 rows does inserted have 5 rows in it?
Yes if the insert/update was done as a single statement.
> if htats the case how do you check field values for every row
> I was using
> if (select newfield from #inserted) = this
> begin
> update #inserted set newfield = that
> end
What is "this"? Is the idea to override the values being inserted/updated in
the
trigger? If that is the case, then you need an InsteadOf trigger not an Afte
r
trigger.
> but that isnt going to work if inserted contains all 5 rows, i thought
> inserted only had the current row and it passed through the instead of tri
gger
> 5 times once for each row. if thats not the case how do you do something like[/co
lor]
No. That is not the case. Each *statement* fires the trigger once (ignoring
cascades for the moment). Thus, imagine the statement:
Insert Table(F1...Fn)
Select F1...FN
From Table
That might insert 1000 records with that once statement. That statement will
fire the trigger once and populate the "inserted" table with 1000 records. I
f it
is an update, then you will get 1000 records in the "inserted" table and 100
0
records in the "deleted" table.
> for each newfield in #inserted do
> if newfield is this
> set it to this.
Can't do that with an After trigger. You need to do that with an InsteadOf
trigger.
> for example
> say i have an insert with three fields
> category, categoryid, name
> and the 3 rows in my insert are
> ('Standard', 1, 'Toys')
> ('NonStandard, null, 'Games')
> ('Misc', null, 'Puzzles')
> and in my instead of trigger i want to fill the nulls with the proper numb
er
> so in my instead of trigger i say
> if (field2 is null)
> begin
> set field2 = (select rightnumber from mastertable where name = field2)
> end
Create Table Stuff
(
Category VarChar(50) Not Null
, SomeNumber Int Null
, Description VarChar(50) Not Null
)
Create Table SomeOtherTable
(
SingleValue Int
)
Insert SomeOtherTable(SingleValue) Values(99)
Create Trigger trigStuff On dbo.Stuff
Instead Of Insert
As
Begin
Insert Stuff(Category, SomeNumber, Description)
Select Category
, (Select SingleValue From SomeOtherTable)
, Description
From inserted As I
End
Insert Stuff(Category, SomeNumber, Description) Values('Standard', 1, 'Toys'
)
Insert Stuff(Category, SomeNumber, Description) Values('NonStandard', Null,
'Games')
Insert Stuff(Category, SomeNumber, Description) Values('Misc', Null, 'Puzzle
s')
Select * From Stuff
HTH
Thomas|||Chris M wrote:
> MGFoster wrote:
>
>
> A table called generators
> create table generators (
> generator_name varchar(50),
> generator_lastid integer
> )
>
> in my table say myTable if i want to get the next ID from generators i
> have to
> select generator_lastid from generators where generator_name =
> 'gen_id_mytable)
> so i do not know how i can join on that, and I'm sure this does violate
> some rule, but its meant to simulate the sequence/generator object of
> oracle/interbase/firebird
--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
Ah... In that case you can just insert that value "generator_lastid"
into the NULL columns in the original table like this (this is in the
trigger):
UPDATE original_table
SET null_column = (SELECT generator_nextid FROM generators
WHERE generator_name = null_column_name)
WHERE id IN (SELECT id FROM inserted WHERE null_column IS NULL)
Substitute correct table/column names where appropriate.
Each row would get the same number. This won't work if you want
incrementing numbers in each row that had the NULL valued column. There
is no way to increment the nextid for the next call. A function can't
be used 'cuz ya can't run an UPDATE inside a function (to increment the
nextid). A procedure can't be used 'cuz ya can't use a procedure as a
recordsource, like ya can w/ a function.
Looks like (ugh!) a WHILE loop would have to be used to cycle thru all
the inserted rows that had NULL values in the column.
@.count = (select count(*) from inserted where column_name is null)
while @.count > 0 begin
-- do the update & generate new nextid
@.count = @.count - 1
end
Quite a problem.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQmhsb4echKqOuFEgEQL6JwCguTf9eD2kFh7F
fZDJAtnUhIKvKWwAniqR
xD1fe46JZ8B2jXX11NQRQmtJ
=xD8y
--END PGP SIGNATURE--
with the state of