SQL server instead of trigger

[zachster17]

Hello all,

I have 2 tables (tblAddProviderProfessional and tblAddProvider). There are other tables such as tblAddProviderFacility and so forth. tblAddProvider is a table that has similar data for all types of requests to add provider into the database. The other tables (i.e. tblAddProviderProfessional) has data that is unique to Professionals being added into the database. I'm trying my first attempt at Instead of triggers and can't quite get it to work.

Below is the code I'm using right now:

-- ================================================
-- Template generated from Template Explorer using:
-- Create Trigger (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- See additional Create Trigger templates for more
-- examples of different Trigger statements.
--
-- This block of comments will not be included in
-- the definition of the function.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
CREATE TRIGGER pmf_trgProviderAddProf_add> 
   ON  pmf_vwProviderAddProf_add
   INSTEAD OF INSERT
AS 
BEGIN
 
Declare @AddID    int
 
    SET NOCOUNT ON;
 
    -- Add record first to main Provider Add Table
    INSERT INTO pmf_tblAddProvider
        (EffectiveDate, RequestRep, SubmitDate, AttachedDoc, PublishInDir,
        TaxID1,TaxID2,PrimaryLocContact1, ContactType1, ContactType2, ContactType3,
        ContactType4, ContactAddrOne1, ContactAddrOne2, ContactAddrOne3, ContactAddrOne4,
        ContactAddrTwo1, ContactAddrTwo2, ContactAddrTwo3, ContactAddrTwo4, ContactCity1,
        ContactCity2, ContactCity3, ContactCity4, ContactState1, ContactState2, ContactState3,
        ContactState4, ContactZip1, ContactZip2, ContactZip3, ContactZip4, ContactPhone1,
        ContactPhone2, ContactPhone3, ContactPhone4, ContactPhoneExt1, ContactPhoneExt2,
        ContactPhoneExt3, ContactPhoneExt4, ContactFax1, ContactFax2, ContactFax3, ContactFax4,
        Contact2SameAs01, Contact4SameAs01, NetworkEncore, NetworkEncircle, NetworkOther,
        NetworkOtherValue, NetworkRejectUSA, NetworkRejectMPlan, GroupXRef1, GroupXRef2,
        GroupXRef3, NpiValue1, NpiValue2, NpiType1, NpiType2, Comments)
    SELECT
        (EffectiveDate, RequestRep, SubmitDate, AttachedDoc, PublishInDir,
        TaxID1,TaxID2,PrimaryLocContact1, ContactType1, ContactType2, ContactType3,
        ContactType4, ContactAddrOne1, ContactAddrOne2, ContactAddrOne3, ContactAddrOne4,
        ContactAddrTwo1, ContactAddrTwo2, ContactAddrTwo3, ContactAddrTwo4, ContactCity1,
        ContactCity2, ContactCity3, ContactCity4, ContactState1, ContactState2, ContactState3,
        ContactState4, ContactZip1, ContactZip2, ContactZip3, ContactZip4, ContactPhone1,
        ContactPhone2, ContactPhone3, ContactPhone4, ContactPhoneExt1, ContactPhoneExt2,
        ContactPhoneExt3, ContactPhoneExt4, ContactFax1, ContactFax2, ContactFax3, ContactFax4,
        Contact2SameAs01, Contact4SameAs01, NetworkEncore, NetworkEncircle, NetworkOther,
        NetworkOtherValue, NetworkRejectUSA, NetworkRejectMPlan, GroupXRef1, GroupXRef2,
        GroupXRef3, NpiValue1, NpiValue2, NpiType1, NpiType2, Comments FROM Inserted)
 
    -- Get identity value (from the Add Provider record)
    SET @AddID = @@IDENTITY
 
    -- Add record next to the Professional Table
    INSERT INTO pmf_tblAddProviderProf
        (AddID, LastName, FirstName, MiddleInitial, MedicalTitle, DateOfBirth, SSN, Gender,
        Specialty1, Specialty2, FacilityBased, CredApproval, FeeSchedID1, FeeSchedID2, FeeSchedID3,
        DefaultDisc1, DefaultDisc2, DefaultDisc3, AnesMulti1, AnesMulti2, AnesMulti3)
    SELECT
        (@AddID, LastName, FirstName, MiddleInitial, MedicalTitle, DateOfBirth, SSN, Gender,
        Specialty1, Specialty2, FacilityBased, CredApproval, FeeSchedID1, FeeSchedID2, FeeSchedID3,
        DefaultDisc1, DefaultDisc2, DefaultDisc3, AnesMulti1, AnesMulti2, AnesMulti3 FROM Inserted)        
 
END
GO
 

And the errors I get are:

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '>'.
Msg 102, Level 15, State 1, Line 26
Incorrect syntax near ','.
Msg 102, Level 15, State 1, Line 47
Incorrect syntax near ','.

Again, I'm very new to SQL Server so my issues are probably relatively easy to solve; let me knwo if you'd like any more info!

Thanks,

Zach

[zachster17]

Another note is that this trigger is being used to insert a record into a view that combines the fields for both tables.

[ck9663]

check your script, there's a "greater than" at the end of your create line...

"CREATE TRIGGER pmf_trgProviderAddProf_add>"

try deleting it....

-- ck

[zachster17]

Ck,

Thanks; that was a rookie mistake. I did that but I'm still getting the other errors; any ideas?

[ck9663]

i think your error is on your INSERT....

i think it's

INSERT INTO TABLE (COL1, COL2) SELECT COL1, COL2 FROM MYTABLE

or

INSERT INTO TABLE (COL1, COL2) (SELECT COL1, COL2 FROM MYTABLE)

NOT

INSERT INTO TABLE (COL1, COL2) SELECT (COL1, COL2 FROM MYTABLE)

either remove your parenthesis after the SELECT part or place it before the SELECT keyword, not after....

-- ck