sql trigger cannot insert row using variable

Question

When i use this code it inserts row

create trigger [dbo].[InsertInvPayment] on dbo.LG_001_01_PAYTRANS
after update
as
begin
  SET NOCOUNT ON;
   declare @InvLogicalRef int;
   declare @InvNumber varchar(50);
   select @InvLogicalRef = inserted.FICHEREF from inserted
   select @InvNumber = dbo.LG_001_01_INVOICE.DOCODE from dbo.LG_001_01_INVOICE where      dbo.LG_001_01_INVOICE.LOGICALREF = @InvLogicalRef
   insert into dbo.CRMINVPAYMENT(INVNUMBER) values('Hello')
  end

if i change it like this

create trigger [dbo].[InsertInvPayment] on dbo.LG_001_01_PAYTRANS
after update
as
begin
  SET NOCOUNT ON;
   declare @InvLogicalRef int;
   declare @InvNumber varchar(50);
   select @InvLogicalRef = inserted.FICHEREF from inserted
   select @InvNumber = dbo.LG_001_01_INVOICE.DOCODE from dbo.LG_001_01_INVOICE where      dbo.LG_001_01_INVOICE.LOGICALREF = @InvLogicalRef
   insert into dbo.CRMINVPAYMENT(INVNUMBER) values(@InvNumber)
  end

it doesn't work. i couldn't find a mistake in the second

"It doesn't work" isn't very descriptive. Error message? Unexpected results? Also, it's broken anyway. inserted may contain 0, 1 or many rows. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Oct 2, 2013 at 9:04

Chamal · Accepted Answer · 2013-10-02 09:39:48Z

1

Seems like You are missing the source for select InvLogicalRef. It should be

select @InvLogicalRef = inserted.FICHEREF from inserted

Also with this trigger you are assuming that only one record will update at once. This will fail for bulk updates.

answered Oct 2, 2013 at 9:39

Chamal

1,44910 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mentatmatt · Accepted Answer · 2013-10-02 09:48:43Z

0

The line where you assign a value to @InvLogicalRef is incomplete. As that variable doesn't contain a value, @InvNumber can't be assigned its value and you are therefore trying to insert a NULL string into CRMINVPAYMENT.

Make sure you select the value of @InvLogicalRef from the inserted table.

answered Oct 2, 2013 at 9:48

Mentatmatt

5255 silver badges13 bronze badges

Collectives™ on Stack Overflow

sql trigger cannot insert row using variable

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related