TRIGGER CREATION USING LINKED SERVER TABLE ON MS SQL

Question

I am trying to create a trigger using linked database to my Oracle Database. If I do select statement, it works that means my Linked Server is working perfectly but when I try to create trigger with it i get this error:

The object name 'TESTS..TESTSMS.YELL_CAT' contains more than the maximum number of prefixes. The maximum is 2.

See my query below.

CREATE TRIGGER Insert_Into_TempYellCat ON TESTS..TESTSMS.YELL_CAT 
after INSERT AS
BEGIN

    INSERT INTO 
    TempYellCat
    (
        TRA_DATE,
        TRA_SEQ1,
        TRA_SEQ2
    )
    SELECT 
        TRA_DATE, 
        TRA_SEQ1, 
        TRA_SEQ2
    FROM 
        TESTS..TESTSMS.YELL_CAT 
END

Please I need someone to assist.

Hint: uppercase is perceived as screaming. You want to edit your question title now! — GhostCat, Jul 18 '17 at 14:19
You can't create a (SQL Server) trigger on a remote database - and definitely not one running a different database product. — Damien_The_Unbeliever, Jul 18 '17 at 14:21

score 0 · Answer 1 · answered Jul 18 '17 at 14:26

0

This is a dupe question. Here is the solution on SO 2 years ago.

The object name contains more than the maximum number of prefixes. The maximum is 3

Specify your schema name SQL05.ManufacturingPortal.dbo.[OPC.WriteRequests] and object name with square brackets

answered Jul 18 '17 at 14:26

Isaiah3015

493
3
11

This is in *no way* an answer. At best it would be a VTC as dupe. However, it also drastically misunderstands the question. – Damien_The_Unbeliever Jul 18 '17 at 16:43

TRIGGER CREATION USING LINKED SERVER TABLE ON MS SQL

1 Answers1