Trigger to capture the new record only

21 Oct 2005 11:58 AM

MartinTeefy

Hi,

I want to write the second field from customer table (cm_name) to another
table to act as a lookup. This table LICENCINGLOOKUP has two columns
(LookupType,LookupValue)

For this insertion the first is set to 'CUSTOMER' but it appears all records
from the customer table are trying to be inserted causing a constraint on the
LicencingLookup table!!! I can't refer to the column name CM_NAME according
to the syntax checker and I can't get the the new CM_NAME value as the key
CM_CUSTOMER_CODE on Customers is varchar 15 so functions like MAX do not work
even though the values stored are numeric!!! (I inherited the system)

Trigger:

CREATE TRIGGER [LogNewCustomer] ON [dbo].[CUSTOMERS]
FOR INSERT
AS

Declare @Type varchar(15), @Value varchar(50)
Select @Type = 'CUSTOMER'

/* next statement selects all numeric key fields */
Select @Value = cm_name from Customers
where cm_customer_code not in ('NewWebCustomer','None')
ORDER by Cm_Name

INSERT INTO LICENCINGLOOKUP(LookupType,LookupValue)
VALUES(@Type,@Value)

Why can't I refer to Customers.CM_NAME on the last line instead of @Value
????
or is there a way to get the value of CM_NAME currently being inserted???

Any Help appreciated

21 Oct 2005 12:35 PM

Dan Guzman

> /* next statement selects all numeric key fields */
> Select @Value = cm_name from Customers
> where cm_customer_code not in ('NewWebCustomer','None')
> ORDER by Cm_Name

This SELECT statement retrieves the value from only one row. Also, you need
to use the pseudo 'inserted' table to identify the newly inserted rows.
Untested example:

CREATE TRIGGER [LogNewCustomer] ON [dbo].[CUSTOMERS]
FOR INSERT
AS
SET NOCOUNT ON

INSERT INTO LICENCINGLOOKUP(LookupType,LookupValue)
SELECT 'CUSTOMER', Cm_Name
FROM inserted
WHERE NOT EXISTS
(
SELECT *
FROM LICENCINGLOOKUP
WHERE
LICENCINGLOOKUP.Tyype = 'CUSTOMER' AND
LICENCINGLOOKUP.LookupValue = inserted.Cm_Name
)
GO

--
Hope this helps.

Dan Guzman
SQL Server MVP

Show quote

"MartinTeefy" <MartinTe***@discussions.microsoft.com> wrote in message
news:5C401CF4-730C-43D0-9A24-2DED7F39F1A6@microsoft.com...
> Hi,
>
> I want to write the second field from customer table (cm_name) to another
> table to act as a lookup. This table LICENCINGLOOKUP has two columns
> (LookupType,LookupValue)
>
> For this insertion the first is set to 'CUSTOMER' but it appears all
> records
> from the customer table are trying to be inserted causing a constraint on
> the
> LicencingLookup table!!! I can't refer to the column name CM_NAME
> according
> to the syntax checker and I can't get the the new CM_NAME value as the key
> CM_CUSTOMER_CODE on Customers is varchar 15 so functions like MAX do not
> work
> even though the values stored are numeric!!! (I inherited the system)
>
> Trigger:
>
> CREATE TRIGGER [LogNewCustomer] ON [dbo].[CUSTOMERS]
> FOR INSERT
> AS
>
> Declare @Type varchar(15), @Value varchar(50)
> Select @Type = 'CUSTOMER'
>
> /* next statement selects all numeric key fields */
> Select @Value = cm_name from Customers
> where cm_customer_code not in ('NewWebCustomer','None')
> ORDER by Cm_Name
>
> INSERT INTO LICENCINGLOOKUP(LookupType,LookupValue)
> VALUES(@Type,@Value)
>
>
> Why can't I refer to Customers.CM_NAME on the last line instead of @Value
> ????
> or is there a way to get the value of CM_NAME currently being inserted???
>
> Any Help appreciated

21 Oct 2005 12:39 PM

David Portas

Why do you want to duplicate data in the "lookup" table? This looks
suspiciously like a One True Lookup Table (multiple attributes in the
same column), which is a fundamentally poor design. If I'm wrong then I
would have expected a foreign key between the two, in which case you
wouldn't want a trigger.

First thing to understand about triggers is that they fire once per
statement, NOT per row. So your trigger is useless if more than one row
is updated. You need to reference the virtual tables INSERTED and
DELETED that contain the updated and prior states of the changed rows.
For example:

CREATE TRIGGER [LogNewCustomer] ON [dbo].[CUSTOMERS]
FOR INSERT AS

INSERT INTO licensinglookup (lookuptype, lookupvalue)
SELECT DISTINCT 'CUSTOMER', cm_name
FROM inserted AS I
WHERE NOT EXISTS
   (SELECT *
    FROM licensinglookup AS L
    WHERE L.lookupvalue = I.cm_name
     AND L.lookuptype = 'CUSTOMER') ;

GO

(untested)

As I say, it seems like eliminating the redundant data would be a
better solution.

--
David Portas
SQL Server MVP
--