Trigger with IF statement

[amishraa]

The idea is to check whether if phone number already exists(data table). If the number exists, then it will create a new row under another table(output table) with the customer ID for that customer.

Table data is where pre-loaded phone numbers are stored.
Table input is the table where feed is being stored.
Table output is the table where the customer id will be stored IF the phone number is found on the data table.

The following is the trigger:

ALTER trigger [dbo].[test_trigger] on [dbo].[input]
after insert
as
set nocount on

IF exists (
    SELECT phone
    FROM data)

insert into output (id, name, phone)
    SELECT id, name, phone FROM input

These are the two things I am trying to tackle:
- How do I set the if exists to actually match the phone number from data table, as opposed to simply checking whether or not if there is any data on that field?
- When the expression comes true, how do I make it enter only that last row on the output table instead of entering all the pre-existing rows?

[john.knapp]

How do I set the if exists to actually match the phone number from data table, as opposed to simply checking whether or not if there is any data on that field?

Assuming your input has been validated so that it has to be either an exact match or a completely different number (i.e., [phone], 202-123-4567, and 202.123.4567 are all the same real phone number)

In your IF EXISTS statement, add a WHERE [data].[phone] = [input].[phone]

When the expression comes true, how do I make it enter only that last row on the output table instead of entering all the pre-existing rows?

This is probably because you're pulling all the records, instead of just the one that matches the phone number.

[amishraa]

ALTER TRIGGER [dbo].[test_trigger1] on [dbo].[testinput]
AFTER INSERT
AS
SET NOCOUNT ON

INSERT INTO testoutput(name)
SELECT DISTINCT i.name
FROM   testinput i
INNER JOIN testdata d
ON i.phone LIKE d.phone

It is working with this code. However, it keeps returning true even if the new row does not match the criteria because it scans the entire table. How could I make it only read the last row on the table?

[john.knapp]

I'm pretty sure you can't make it read only the last row...

If you only want one row returned, then adjust your query accordingly.

By the way, never use a LIKE if you can avoid it - that will bring even the heftiest server to it's knees if there is enough data.

[amishraa]

Thanks. I totally overlooked that. Being that they are phone numbers I definitely do NOT want to use LIKE.
Is there a workaround to that so it does not think the statement is true when the newly added row does not have the matching data on another table?

[john.knapp]

If it's returning duplicate data, then you have duplicated values in whatever column you are defining your join on, in one table or both...

[amishraa]

Let me try making the objective of this trigger more clear.

Input data table gets new row. The trigger is supposed to check the latest row there and check if the phone number on this table matches the one on the data table. If the phone number matches, then it is supposed to add a new row under the output table with the name of the customer found on the input table.
If no phone number matches, then do nothing.

It is almost working, except it is doing full table scan each time trigger is running so it is returning phone number match even when the last row on the input table does not have matching phone number with data table.

[john.knapp]

it is doing full table scan each time trigger is running

Doing a table scan, as in query analyzer says "Table Scan" or looking through the whole table to see if there's a matching phone number (btw, you'll want an index on that column if you don't already have one) - Because "Table Scan" is bad - see previous reference to LIKE keyword...

So, if I understand - even if the phone number is already in the data table, don't match unless it is in the last row?

If you just inserted that last row, and you have a primary key on that table, and you're in the same scope (with triggers, you should be), use scope_identity() to get the primary key, and use that in your WHERE clause. Something like this:

ALTER TRIGGER [dbo].[test_trigger1] on [dbo].[testinput]
AFTER INSERT
AS
SET NOCOUNT ON

DECLARE @LastIdentity int                      -- assumes default int datatype for your primary key
SELECT @LastIdentity = Scope_Identity()

INSERT INTO testoutput(name)
SELECT i.name
FROM   testinput i
INNER JOIN testdata d
ON i.phone LIKE d.phone
WHERE i.[YOUR_IDENTITY_COLUMN_NAME] = @LastIdentity

[amishraa]

Only match the phone number from the last row of the input table but match any row on the data table.

[amishraa]

That did not return any value at all even when the data table has a matching record from the last row.
For "Your_Identity_Column_Name" I entered the column name "Phone" which is what I am trying to match.
I have a column Id set as primary key on the input and output table.
Data table only has one column called phone. (As this is only for testing purposes).

[john.knapp]

Scope_Identity() is always the primary key value of the last insert operation in the current user scope. You need a primary key on your data table or you're going to have issues. I'm too tired at the moment to think about whether that is causing issues in this particular query, but I think not - as long as you have a limited amount of data that you're working with anyway - say 10-15 numbers for testing.

For "Your_Identity_Column_Name" I entered the column name "Phone" which is what I am trying to match.

Nope. Use whatever you called your primary key column in the input : [Input].[ID]

[amishraa]

No worries. I really do appreciate your help nonetheless. You have my vote! :)

[john.knapp]

Ok, thanks and good luck!