I am currently working on a MS SQL server 2000.
I would like to access the data inserted or deleted within a trigger. however the built-in tables -- inserted and deleted -- are not accessible. anyone knows why? And is there any other way to do this?
Thankspost your t-sql code that you used to access the inserted/deleted tablessql
I'm hoping someone has seen this before because I have no idea what could be causing it.
I have an SQL 2005 database with multiple tables and several triggers on the various tables all set to run after insert and update.
My program inserts a record into the "items" via a SP that returns the index of the newly added row. The program then inserts a row into another table that is related to items. When the row is inserted into the second table it gets an error that it cannot insert the record because of a foreign key restraint. Checking the items table shows the record that was just inserted in there is now deleted.
The items record is only deleted when I have my trigger on that table enabled. Here is the text of the trigger:
GO
SETANSI_NULLSON
GO
SETQUOTED_IDENTIFIERON
GO
AS
BEGIN
SETNOCOUNTON;
INSERTINTO tblHistory(table_name, record_id, is_insert)
VALUES('items', 123, 1)
END
tblHistory's field types are (varchar(50), BigInt, bit).
As you can see there is nothing in the trigger to cause the items record to be deleted, so I have no idea what it could be? Anyone ever see this before?
Thanks in advance!
Hey,
I don't know that the row is deleted, but that the row doesn't get actually inserted for some reason. What do the two insertions look like? In SQL or ADO.NET code? Could it be that the first item doesn't get inserted, then returns a number that doesn't match an entry in that table, and that is why you get an error for the second insert?
|||No, the first item is inserted and the returned value is exactly what it should be. When we test it without the trigger enabled and it all works, the new primary key value is the next value after the one that disapeared (i.e. if the record that was deleted was 5 the next one that works is 6).
|||Are you using @.@.identity?
|||If one of the follow on triggers fails, for whatever reason, the insert statement will be rolled back.
I suggest commenting out the triggers one by one (from last run to first run) until you figure out which one is the problem.
(Or learn to use the debugger in sql server.)
|||David is correct in that the trigger code is considered part of the insert transaction.
If the trigger fails, then the entire "transaction" is rolled back, including the insert. If tblHistory has a foreign key constraint, and the trigger fails because of it, then you will get exactly what you are describing. The record is inserted partially (uncommitted), the trigger is fired, an error is encountered, then the insert is rolled back and the error from the trigger is sent to the client.
|||It couldn't have been the trigger failing, b/c there are no constraints on the history table and while yo uare correct the error would have been returned as if it was coming from the insert statement I said above "When the row is inserted into the second table it gets an error that it cannot insert the record because of a foreign key restraint." The error was not about hte history table.
Motley you actually had the answer. What was happening is the stored procedure ran and inserted the row into items, the trigger ran on that and inserted the row into web updates, the stored procedure then returned the @.@.Identity, but since that returns the last identity of any insert to the database it was returning the identity of the history table, not the items table. When the second insert was run it was trying to insert the wrong identity and failed the foreign key restraint, resulting in the entire transaction to fail and rollback, giving the appearance the items record had been deleted.
Thanks for your help!
|||Use scope_identity(), not identity! scope_identity was created to avoid just this problem!
sqlhi
for after trigger the records stored in followig table
inserted and deleted table.
but i want to know where this tables physically stored ...i mean in which database master or some other database?
and 2nd thing tigger fired for each row or for only insert,delete,update statement?
thanx
Where stored?
Obviously in temp tables at tempdb.
Is it executed for each row?
No. If single query affects more than one row, the inserted or deleted table may have more than one row. When you write a trigger you have to keep consider this & you have to handle your trigger query which will support both single row & multiple rows.
|||The table is not physically stored it is virtual only, it only exists within the trigger context. Triggers are fired per statement not per row, you will need to handle mutlirow existance in your trigger and in addition the occurence of no affected rows,a s the trigger is also fired if no rows is affected like
Code Snippet
UPDATE SomeTable SET SomeColumn = 'SomeValue' WHERE 1=2Jens K. Suessmeyer
http://www.sqlserver2005.de
|||Is it executed for each row?
No. If single query affects more than one row, the inserted or deleted table may have more than one row. When you write a trigger you have to keep consider this & you have to handle your trigger query which will support both single row & multiple rows.
mani
i mean trigger fired for each row or only for update statement..here i m not talking about inserted and deleted table
|||On high level it is called virtual, but SQL Server always use the TempDB as workspace to store the data, so the data may be presented or stored in tempdb but you can't access these data from outside of your trigger scope & these are absolutely read-only.
There is interesting thread on same question on DB Engine forum
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=908238&SiteID=1
|||The answer is for one statement not for each rows.
|||thanx mani and jens.
with the state of with the state ofTry something like:
CREATE TABLE Test ( id int not null identity (1,1), Description char(10) )
CREATE TRIGGER Test_Insert ON Test FOR INSERT AS
UPDATE TEST SET Description = CASE Id%2 WHEN 0 THEN UPPER(Description) ELSE
LOWER (Description) END
INSERT INTO TEST ( Description ) VALUES ('One')
INSERT INTO TEST ( Description ) VALUES ('Two')
INSERT INTO TEST ( Description ) VALUES ('Three')
INSERT INTO TEST ( Description ) VALUES ('four')
INSERT INTO TEST ( Description ) VALUES ('five')
INSERT INTO TEST ( Description ) VALUES ('SIX')
INSERT INTO TEST ( Description ) VALUES ('SEVEN')
SELECT * from Test
John
<imani_technology@.yahoo.com> wrote in message
news:f9208446.0309011615.5f269625@.posting.google.c om...
> How would I write a trigger that updates the values of a Description
> column to upper case for even IDs and to lower case for odd IDs? I
> need this trigger to fire for INSERT and UPDATE events.|||imani_technology@.yahoo.com wrote in message news:<f9208446.0309011615.5f269625@.posting.google.com>...
> How would I write a trigger that updates the values of a Description
> column to upper case for even IDs and to lower case for odd IDs? I
> need this trigger to fire for INSERT and UPDATE events.
You might want to consider formatting the text on the client, when you
retrieve it from the database - presentation tasks don't really belong
in a database. But if you want to do it using a trigger, something
like this should work (assuming your 'ID' is an integer key column):
create trigger ATR_UI_MyTable
on dbo.MyTable after insert, update
as
update dbo.MyTable
set DescriptionColumn =
case i.IDColumn % 2
when 1 then lower(i.DescriptionColumn)
when 0 then upper(i.DescriptionColumn)
end
from dbo.MyTable t
join inserted i
on t.IDColumn = i.IDColumn
Simon
Does anyone know of a simple way to do this? I want to create an
insert trigger for a table and if the record already exists based on
some criteria, I want to update the table with the values that are
passed in via the insert trigger without having to use all the 'set'
statements for each field (so if we add fields in the future I won't
have to update the trigger). In other words, I want the trigger code
to look something like this:
if exists (select * from TableA where Fld1 = inserted.Fld1) then
//don't do insert, do an update instead (would i want to rollback here?
and will I have access to the 'inserted' table still?)
Update TableA
Set TableA.<all the fields> = Inserted.<all the fields>
where Fld1 = inserted.Fld1
end if
Any help or ideas would be appreciated.
Thanks,
TeresaUPDATE requires that you specify the columns by name. It's best
practice to do so in an INSERT statement too.
Always specify the column names. In the long run this will improve
reliability and save you development time.
--
David Portas
SQL Server MVP
--|||takilroy@.yahoo.com wrote:
> Hi,
> Does anyone know of a simple way to do this? I want to create an
> insert trigger for a table and if the record already exists based on
> some criteria, I want to update the table with the values that are
> passed in via the insert trigger without having to use all the 'set'
> statements for each field (so if we add fields in the future I won't
> have to update the trigger). In other words, I want the trigger code
> to look something like this:
> if exists (select * from TableA where Fld1 = inserted.Fld1) then
> //don't do insert, do an update instead (would i want to rollback here?
> and will I have access to the 'inserted' table still?)
> Update TableA
> Set TableA.<all the fields> = Inserted.<all the fields>
> where Fld1 = inserted.Fld1
> end if
> Any help or ideas would be appreciated.
> Thanks,
> Teresa
Nice hack.
A rollback is no good because you'd lose the update as well. But you could
delete the inserted row.
You may also have issues with primary keys and other constraints. If a
constraint fires before the trigger, your insert will fail on a pk
constraint and your clever trigger will never fire.
Finally, the performance issue is real. Doing the insert, deleting it, and
then updating causes only one real write to the table that has to be
committed, but carries three complete journal writes. An update only
carries two discreet write. It might be worthwhile to pump a few million
operations in each combination so you can at least speak knowledgeably
about what the real performance price is.
--
Kenneth Downs
Secure Data Software, Inc.
(Ken)nneth@.(Sec)ure(Dat)a(.com)|||On 12 May 2005 08:50:10 -0700, takilroy@.yahoo.com wrote:
>Hi,
>Does anyone know of a simple way to do this? I want to create an
>insert trigger for a table and if the record already exists based on
>some criteria, I want to update the table with the values that are
>passed in via the insert trigger without having to use all the 'set'
>statements for each field (so if we add fields in the future I won't
>have to update the trigger). In other words, I want the trigger code
>to look something like this:
>if exists (select * from TableA where Fld1 = inserted.Fld1) then
>//don't do insert, do an update instead (would i want to rollback here?
>and will I have access to the 'inserted' table still?)
> Update TableA
> Set TableA.<all the fields> = Inserted.<all the fields>
> where Fld1 = inserted.Fld1
>end if
>Any help or ideas would be appreciated.
>Thanks,
>Teresa
Hi Teresa,
There is no way to avoid listing the columns in an UPDATE statement. If
there were, I'd recommend against it (just as I recommend against using
SELECT * or INSERT without column-list in production code).
Also, your trigger's pseudo-code will do an update for all rows that
were inserted if at least one of them exists in TableA. You could remove
the existance check; the effect will be the same (rows that are not in
TableA won't be changed, rows that are will be - and if no row in
inserted is also in TableA, nothing changes in TableA), but it will
somewhat improve performance.
Also, Kenneth is correct - constraints are checked before the trigger is
executed. The only way around that is to use an INSTEAD OF trigger that
updates rows that are already present and inserts rows that are not yet
present.
If you need help transforming this to an INSTEAD OF trigger, just
holler.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
create table testtrigger(id integer unique, b integer, c integer)
go
create trigger testtrigger_ins on testtrigger
for insert as
update testtrigger set c = (select ...some_function_of_b... from
testtrigger t1,inserted t2
where t1.id = t2.id)
where id in (select id from inserted);
go
where id is testrigger's unique id field, and c is a field derived from
b.
This seems terribly inefficient since each insert results in an extra
select and update. And if the table is large and unindexed (which it
could be if we are bulk loading) then I would imagine this would be
very slow.
Are there any better ways of doing this?
Many thanks,...
Mike Dunham-WilkieCREATE table ><>>Look at computered columns is your answer . BTW no
data is stored for this type of column.
e.g. CREATE TABLE mytable
(
low int,
high int,
myavg AS (low + high)/2
Duncan|||On 22 Sep 2006 13:11:12 -0700, mike@.barrodale.com wrote:
Quote:
Originally Posted by
>I'm looking for an efficient way to populate derived columns when I
>insert data into a table in SQL Server. In Informix and PostgreSQL
>this is easily done using the "for each row..." syntax, but all I've
>been able to come up with for SQL Server is the following:
Quote:
Originally Posted by
>This seems terribly inefficient since each insert results in an extra
>select and update. And if the table is large and unindexed (which it
>could be if we are bulk loading) then I would imagine this would be
>very slow.
>
>Are there any better ways of doing this?
Since SQL Server is optimized for set-based operations, you'll probably
find the speed of these operations to be quite adequate in most cases.
Here's an alternate syntax of the UPDATE statement that might result in
even faster operation. Note, though, that this syntax has some quirks,
especially if there's not a guaranteed one to one mapping of rows in the
update target and the source of the data.
UPDATE t
SET c = some_function_of_b
FROM testtrigger AS t
INNER JOIN inserted AS i
ON i.id = t.id
But only use this if you have a solid reason for being unable to use a
computed column, as Duncan ("undercups") demonstrates in his reply!
--
Hugo Kornelis, SQL Server MVP
