Prev: Synonyms
Next: ddl not working in scrip
From: Cory J. Laidlaw, Beyond01.com on 18 Jun 2010 12:49
Hi,

I've written a trigger to capture a field I need when a record is deleted,
and insert it into another table. The problem is I don't want this trigger to
do anything if the field in question is null.

I have written the following...

ALTER trigger [dbo].[EEI_LedgerAP_Delete]
on [dbo].[LedgerAP]
for Delete
as
Begin

if Deleted.[WBS1] is not null
Insert into EEI_Sync (idtype, idvalue) Select 'PR', LEFT(Deleted.[WBS1], 8)
from Deleted

End

When I click the check syntax button it says it's OK, but when I execute the
SQL, I get this message:

Msg 4104, Level 16, State 1, Procedure EEI_LedgerAP_Delete, Line 7
The multi-part identifier "Deleted.WBS1" could not be bound.

Anyone have any ideas? Thanks much!

Cory
From: Scott Morris on 18 Jun 2010 13:15

"Cory J. Laidlaw, Beyond01.com"
<CoryJLaidlawBeyond01com(a)discussions.microsoft.com> wrote in message
news:2D49AC3B-1E85-402F-A8D5-7E6C51696A9E(a)microsoft.com...
> Hi,
>
> I've written a trigger to capture a field I need when a record is deleted,
> and insert it into another table. The problem is I don't want this trigger
> to
> do anything if the field in question is null.
>
> I have written the following...
>
> ALTER trigger [dbo].[EEI_LedgerAP_Delete]
> on [dbo].[LedgerAP]
> for Delete
> as
> Begin
>
> if Deleted.[WBS1] is not null
> Insert into EEI_Sync (idtype, idvalue) Select 'PR', LEFT(Deleted.[WBS1],
> 8)
> from Deleted

If exists (select * from deleted where WBS1 is not null)
Insert into EEI_Sync (idtype, idvalue) Select 'PR', LEFT(Deleted.[WBS1],
8)
from Deleted where Deleted.[WBS1] is not null

Note the you must check for NULLs within the insert statement regardless of
the result of the IF statement. This is because the trigger executes at a
statement level, not at the row level, and there could be a mix of values
(null and not null) in the affected rows. You could leave off the IF
statement, but be aware that the insertion of an empty resultset still fires
the insert triggers on the inserted table.


 | 
Pages: 1
Prev: Synonyms
Next: ddl not working in scrip