Hi,

I have to tables : Rank and Item
Rank contains a FK named R_I_id which references Item's primary key
named I_id, which is also an identity column.

I want the field R_I_id of table Rank to be updated with the value of
Item's I_id when a new Item is inserted. So I wrote the following
trigger :

CREATE TRIGGER [dbo].[trg_oninsertitem]
ON [dbo].[Item]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON
SET IMPLICIT_TRANSACTIONS OFF
DECLARE @I_id INT
SELECT @I_id = SCOPE_IDENTITY()

BEGIN TRANSACTION
INSERT Rank(R_I_id) VALUES(@I_id)
IF (@@ERROR <> 0) BEGIN
PRINT 'error in trigger trg_oninsertitem'
ROLLBACK TRANSACTION
END
COMMIT
END

But when I insert a value in Item, I got the following message :

Msg 515, Level 16, State 2, Procedure trg_oninsertitem, Line 17
Cannot insert the value NULL into column 'R_T_id', table
'base.dbo.Rank'; column does not allow nulls. INSERT fails.

and I don't understand, because my trigger is fired after the value is
inserted in table Item, so the field I_id should contain a new
identity value, not NULL

my DBMS is SQL Server 2000

thanks in advance for your

> and I don't understand, because my trigger is fired after the value is
> inserted in table Item, so the field I_id should contain a new
> identity value, not NULL

I don't see any reference to I_id in the trigger code. However, there are a
couple of more serious issues that need attention.

First, a trigger fires once per statement rather than once per row. You can
use the inserted table in the trigger code to obtain values of the row(s)
just inserted so that multi-row inserts are properly handled. Second, a
trigger always runs in the context of a transaction so you should not issue
a BEGIN TRAN or COMMIT in a trigger.

The example below will insert the I_id values of the newly inserted rows
into the Rank table.

CREATE TRIGGER [dbo].[trg_oninsertitem]
ON [dbo].[Item]
AFTER INSERT
AS
SET NOCOUNT ON

INSERT dbo.Rank(R_I_id)
SELECT I_id FROM inserted

IF @@ERROR <> 0
BEGIN
PRINT 'error in trigger trg_oninsertitem'
ROLLBACK TRANSACTION
END
GO

--
Hope this s.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"Celine" <xhanrot@gmail.com> wrote in message
news:48ff0fc1-ad32-41ab-b0c9-20f9a274cdaa@e10g2000prf.googlegroups.com...
> Hi,
>
> I have to tables : Rank and Item
> Rank contains a FK named R_I_id which references Item's primary key
> named I_id, which is also an identity column.
>
> I want the field R_I_id of table Rank to be updated with the value of
> Item's I_id when a new Item is inserted. So I wrote the following
> trigger :
>
> CREATE TRIGGER [dbo].[trg_oninsertitem]
> ON [dbo].[Item]
> AFTER INSERT
> AS
> BEGIN
> SET NOCOUNT ON
> SET IMPLICIT_TRANSACTIONS OFF
> DECLARE @I_id INT
> SELECT @I_id = SCOPE_IDENTITY()
>
> BEGIN TRANSACTION
> INSERT Rank(R_I_id) VALUES(@I_id)
> IF (@@ERROR <> 0) BEGIN
> PRINT 'error in trigger trg_oninsertitem'
> ROLLBACK TRANSACTION
> END
> COMMIT
> END
>
> But when I insert a value in Item, I got the following message :
>
> Msg 515, Level 16, State 2, Procedure trg_oninsertitem, Line 17
> Cannot insert the value NULL into column 'R_T_id', table
> 'base.dbo.Rank'; column does not allow nulls. INSERT fails.
>
> and I don't understand, because my trigger is fired after the value is
> inserted in table Item, so the field I_id should contain a new
> identity value, not NULL
>
> my DBMS is SQL Server 2000
>
> thanks in advance for your

>> I have two tables: Rank and Item Rank contains a FK named R_I_id which references Item's PRIMARY KEY named I_id, which is also an IDENTITY column. <<

1) The singular names say that they each have one row in them. Is
this true?

2) They are not really tables at all. IDENTITY is never a relational
key. It is a physical locator counter that increments when an
insertion was attempted on a table. It has nothing to do with the
data model.

3) The table "rank" should not be here; it is an attribute of an
entity and not an entity itself. The name "Items" is also too vague
to be a data element name.

4) You should not be writing triggers except under the most extreme
circumstance. This looks like a case of "attribute splitting" and an
attempt to repair the design flaws with procedural code.

Post a description of what you are trying to do and we will see if we
can get you a workable schema design that does not need extreme
measures.