Create Trigger on 2 tables from 2 differents bases

Question

Hello i'm working on a trigger. This trigger is able to compare data inserted in my table DOUBLON from base F with the data from CLIENT from base T. If there is similar TEL, TELPOR, TELPRO, TELDOM, then it update F.DOUBLON.doublon = 'OUI' else it put in a 'NON'. I created my trigger on on my base F on my table DOUBLON.

There is what I did:

    CREATE TRIGGER DoublonInserted
    ON  dbo.DOUBLON
    AFTER INSERT
    AS 
    DECLARE @doublon INTEGER
    SELECT @doublon = 0
    IF SELECT count(C.TEL)
    FROM T.dbo.CLIENT AS C , inserted AS I
    where I.TEL = C.TEL
    OR I.TEL = C.TELDOM
    OR I.TEL = C.TELPRO
    OR I.TEL = C.TELPOR
    OR I.TELDOM = C.TEL
    OR I.TELDOM = C.TELDOM
    OR I.TELDOM = C.TELPRO
    OR I.TELDOM = C.TELPOR
    OR I.TELPRO = C.TEL
    OR I.TELPRO = C.TELDOM
    OR I.TELPRO = C.TELPRO
    OR I.TELPRO = C.TELPOR
    OR I.TELPOR = C.TEL
    OR I.TELPOR = C.TELDOM
    OR I.TELPOR = C.TELPRO
    OR I.TELPOR = C.TELPOR) > 1
    BEGIN
    UPDATE dbo.DOUBLON
    SET DOUBLON = 'OUI' --, @doublon = 0 
    FROM T.dbo.CLIENT AS C, inserted AS I
    where I.TEL = C.TEL
    OR I.TEL = C.TELDOM
    OR I.TEL = C.TELPRO
    OR I.TEL = C.TELPOR
    OR I.TELDOM = C.TEL
    OR I.TELDOM = C.TELDOM
    OR I.TELDOM = C.TELPRO
    OR I.TELDOM = C.TELPOR
    OR I.TELPRO = C.TEL
    OR I.TELPRO = C.TELDOM
    OR I.TELPRO = C.TELPRO
    OR I.TELPRO = C.TELPOR
    OR I.TELPOR = C.TEL
    OR I.TELPOR = C.TELDOM
    OR I.TELPOR = C.TELPRO
    OR I.TELPOR = C.TELPOR 
    END
    ELSE
    BEGIN
    UPDATE dbo.DOUBLON
    SET DOUBLON = 'NON' --, @doublon = 0
    FROM T.dbo.CLIENT AS C, inserted AS I
    where I.TEL != C.TEL
    OR I.TEL != C.TELDOM
    OR I.TEL != C.TELPRO
    OR I.TEL != C.TELPOR
    OR I.TELDOM != C.TEL
    OR I.TELDOM != C.TELDOM
    OR I.TELDOM != C.TELPRO 
    OR I.TELDOM != C.TELPOR
    OR I.TELPRO != C.TEL
    OR I.TELPRO != C.TELDOM
    OR I.TELPRO != C.TELPRO
    OR I.TELPRO != C.TELPOR
    OR I.TELPOR != C.TEL
    OR I.TELPOR != C.TELDOM
    OR I.TELPOR != C.TELPRO
    OR I.TELPOR != C.TELPOR
    END

In the facts it's "work" Not in reality:

CASE WORKING. Work if there is any duplicate in my insert, trigger set 'NON' in my column doublon for every lines inserted. Work if there is only duplicate in my insert, trigger set 'OUI' in my column doublon for every lines inserted.

CASE NOT WORKING. Not working if there is duplicate and no duplicate inserted.

I tried to reset my var @doublon but still the same.

I noticed that, my trigger put the last result of my test on all line for column doublon. If the latest insert is a duplicate data all line will have 'Yes' (NOT vice-versa).

I would like to have help to explain me why, or another way to do it ( with Trigger).

I can't touch the database T (add column etc forbidden) !

++ Bases are on the same server ++

Thanks for your time and you help !

EDIT :

Table CLIENT

Table DOUBLON

ID_CLIENT -> int

What I want : Set correct value on Doublon

Everything else varchar(50)

Answer 1

Alright ! Here is "the answer" :

    CREATE TRIGGER DoublonInserted 
ON dbo.DOUBLON
INSTEAD OF INSERT AS 
    BEGIN

INSERT INTO DOUBLON
(
    ID_CLIENT
    , NOM
    , PRENOM
    , TEL
    , TELDOM
    , TELPRO
    , TELPOR
    , DOUBLON
)
select i.ID_CLIENT
    , i.NOM
    , i.PRENOM
    , i.TEL
    , i.TELDOM
    , i.TELPRO
    , i.TELPOR
    ,  CASE 
            when I.TEL = C.TEL
OR I.TEL = C.TELDOM
OR I.TEL = C.TELPRO
OR I.TEL = C.TELPOR
OR I.TELDOM = C.TEL
OR I.TELDOM = C.TELDOM 
OR I.TELDOM = C.TELPRO
OR I.TELDOM = C.TELPOR
OR I.TELPRO = C.TEL
OR I.TELPRO = C.TELDOM 
OR I.TELPRO = C.TELPRO
OR I.TELPRO = C.TELPOR
OR I.TELPOR = C.TEL
OR I.TELPOR = C.TELDOM 
OR I.TELPOR = C.TELPRO
OR I.TELPOR = C.TELPOR
            THEN 'OUI'
            ELSE 'NON'
        END
FROM inserted AS I
left join T.dbo.CLIENT AS C 
on I.TEL = C.TEL
OR I.TEL = C.TELDOMICIL
OR I.TEL = C.TELPRO
OR I.TEL = C.TELPOR
OR I.TELDOM = C.TEL
OR I.TELDOM = C.TELDOM
OR I.TELDOM = C.TELPRO
OR I.TELDOM = C.TELPOR
OR I.TELPRO = C.TEL
OR I.TELPRO = C.TELDOM
OR I.TELPRO = C.TELPRO
OR I.TELPRO = C.TELPOR
OR I.TELPOR = C.TEL
OR I.TELPOR = C.TELDOM
OR I.TELPOR = C.TELPRO
OR I.TELPOR = C.TELPOR
END

Thanks to @Sean Lange (you did it !), @Bohemian♦ and everyone else !

Answer 2

I think I understand what you are after now. Here is how you could create this as an instead of trigger. This should be pretty close.

CREATE TRIGGER DoublonInserted 
    ON dbo.DOUBLON
    INSTEAD OF INSERT AS 
BEGIN
    SET NOCOUNT ON;

    INSERT DOUBLON
    (
        ID_CLIENT
        , NOM
        , PRENOM
        , TEL
        , TELDOM
        , TELPRO
        , TELPOR
        , DOUBLON
    )
    select i.ID_CLIENT
        , i.NOM
        , i.PRENOM
        , i.TEL
        , i.TELDOM
        , i.TELPRO
        , i.TELPOR
        , case when 
                I.TEL = C.TEL
                OR I.TEL = C.TELDOM
                OR I.TEL = C.TELPRO
                OR I.TEL = C.TELPOR
                OR I.TELDOM = C.TEL
                OR I.TELDOM = C.TELDOM
                OR I.TELDOM = C.TELPRO
                OR I.TELDOM = C.TELPOR
                OR I.TELPRO = C.TEL
                OR I.TELPRO = C.TELDOM
                OR I.TELPRO = C.TELPRO
                OR I.TELPRO = C.TELPOR
                OR I.TELPOR = C.TEL
                OR I.TELPOR = C.TELDOM
                OR I.TELPOR = C.TELPRO
                OR I.TELPOR = C.TELPOR
            THEN 'OUI'
            ELSE 'NON'
            END
    FROM inserted AS I
    join T.dbo.CLIENT AS C on c.ID_CLIENT = i.ID_CLIENT;
END

Still pretty vague on details but I think you want something like this.

CREATE TRIGGER DoublonInserted 
    ON dbo.DOUBLON
    INSTEAD OF INSERT AS 
BEGIN
    SET NOCOUNT ON;

    INSERT DOUBLON
    (
        ID_CLIENT
        , NOM
        , PRENOM
        , TEL
        , TELDOM
        , TELPRO
        , TELPOR
        , DOUBLON
    )
    select i.ID_CLIENT
        , i.NOM
        , i.PRENOM
        , i.TEL
        , i.TELDOM
        , i.TELPRO
        , i.TELPOR
        ,  CASE 
                when C.TEL IS NULL
                    OR C.TELPRO IS NULL
                    OR C.TELDOM IS NULL
                    OR C.TELPOR IS NULL
                THEN 'NON'
                ELSE 'OUI'
            END
    FROM inserted AS I
    left join T.dbo.CLIENT AS C on I.TEL = C.TEL
                                OR I.TEL = C.TELPRO
                                OR I.TEL = C.TELDOM
                                OR I.TEL = C.TELPOR
END

Answer 3

You need only one "find a double" query:

CREATE TRIGGER DoublonInserted
ON  dbo.DOUBLON
AFTER INSERT
AS BEGIN
IF EXISTS(
    select * from
    FROM T.dbo.CLIENT AS C, inserted AS I
    where I.TEL = C.TEL
    OR I.TEL = C.TELDOM
    OR I.TEL = C.TELPRO
    OR I.TEL = C.TELPOR
    OR I.TELDOM = C.TEL
    OR I.TELDOM = C.TELDOM
    OR I.TELDOM = C.TELPRO
    OR I.TELDOM = C.TELPOR
    OR I.TELPRO = C.TEL
    OR I.TELPRO = C.TELDOM
    OR I.TELPRO = C.TELPRO
    OR I.TELPRO = C.TELPOR
    OR I.TELPOR = C.TEL
    OR I.TELPOR = C.TELDOM
    OR I.TELPOR = C.TELPRO
    OR I.TELPOR = C.TELPOR)
    UPDATE dbo.DOUBLON
    SET DOUBLON = 'OUI'
    WHERE ID_CLIENT = I.ID_CLIENT;
ELSE
    UPDATE dbo.DOUBLON
    SET DOUBLON = 'NON'
    WHERE ID_CLIENT = I.ID_CLIENT;
END