"Instead of" Insert trigger (1 Viewer)

Pat Hartman · Sep 29, 2006

I have very little experience with working directly with SQL Server. Mostly I use Access and let the DBA worry about these things once I have upsized the tables. Unfortunately this client doesn't have a DBA to help me with the more technical aspects of this database creation.

I am using DTS to load a table. Some of the rows have been duplicated. I can't get the mainframe bug fixed so I have to live with it for a while. Can someone post an example of an Instead of Insert trigger that will allow the unique rows to be added and the dups to be discarded quietly. As it is now, I just get an error message and no rows are added. This is of course a "piece of cake" with Jet which inserts the good rows and discards the bad rows. But the table contains over 9 million rows so I don't want to run it through Access first.
Thanks

This is where I am so far:

Code:

CREATE TRIGGER IO_Trig_INS_StdPricing ON TBLSTDPRICING
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON
-- Check for duplicate row. If there is no duplicate, do an insert.
IF (NOT EXISTS (SELECT P.[DATE]
      FROM TBLSTDPRICING P, inserted I
      WHERE P.[DATE] = I.[DATE] AND P.BANK = I.BANK AND P.[APPLICATION] = I.APPLICATION AND P.[PRODUCT TYPE] = I.[PRODUCT TYPE] AND P.[PRICING REGION]= I.[PRICING REGION] AND P.[SERVICE CODE] = I.[SERVICE CODE] AND P.[EFFECTIVE DATE] = I.[EFFECTIVE DATE]
   INSERT INTO TBLSTDPRICING
      SELECT I.*
      FROM inserted
END

Error 156:Incorrect syntax near the keyword INSERT

Pat Hartman · Sep 29, 2006

Nevermind

Never mind. I was missing the ending parens. Then I couldn't use the * to insert all the columns because I had an identity column on the table so I had to list them individually. I have a feeling that there is a way to exclude columns when you use the * but I didn't have time to search for it.

Code:

CREATE TRIGGER IO_Trig_INS_StdPricing ON TBLSTDPRICING
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON
-- Check for duplicate Person. If there is no duplicate, do an insert.
IF (NOT EXISTS (SELECT P.[DATE]
      FROM TBLSTDPRICING P, inserted I
      WHERE P.[DATE] = I.[DATE] 
	AND P.BANK = I.BANK 
	AND P.[APPLICATION] = I.[APPLICATION] 
	AND P.[PRODUCT TYPE] = I.[PRODUCT TYPE] 
	AND P.[PRICING REGION]= I.[PRICING REGION] 
	AND P.[SERVICE CODE] = I.[SERVICE CODE] 
	AND P.[EFFECTIVE DATE] = I.[EFFECTIVE DATE]))
   INSERT INTO TBLSTDPRICING 
      SELECT 	I.DATE
		, I.BANK
		, I.[REGION TYPE]
		, I.APPLICATION
		, I.[PRODUCT TYPE]
		, I.[PRICING REGION]
		, I.[SERVICE CODE]
		, I.[ACTIVITY CHARGE]
		, I.[NUMBER FREE]
		, I.[CHARGE CODE]
		, I.[DEBIT CREDIT CODE]
		, I.[MINIMUM CHARGE]
		, I.[MAXIMUM CHARGE]
		, I.[FLAT CHARGE]
		, I.[CHARGE MARKUP]
		, I.[SERVICE DESCRIPTION NUMBER]
		, I.[NUMBER AMOUNT CODE]
		, I.[TIER OPTION]
		, I.[TIER01 VOL]
		, I.[TIER01 CHRG]
		, I.[TIER01 FLTCD]
		, I.[TIER02 VOL]
		, I.[TIER02 CHRG]
		, I.[TIER02 FLTCD]
		, I.[TIER03 VOL]
		, I.[TIER03 CHRG]
		, I.[TIER03 FLTCD]
		, I.[TIER04 VOL]
		, I.[TIER04 CHRG]
		, I.[TIER04 FLTCD]
		, I.[TIER05 VOL]
		, I.[TIER05 CHRG]
		, I.[TIER05 FLTCD]
		, I.[TIER06 VOL]
		, I.[TIER06 CHRG]
		, I.[TIER06 FLTCD]
		, I.[TIER07 VOL]
		, I.[TIER07 CHRG]
		, I.[TIER07 FLTCD]
		, I.MULTIPLIER
		, I.[PRICE LIST NUMBER]
		, I.[EFFECTIVE DATE]
		, I.[EXPIRATION DATE]
      FROM inserted I
END

WayneRyan · Sep 29, 2006

Pat,

I haven't used an Instead Of trigger, but ...

Code:

CREATE TRIGGER IO_Trig_INS_StdPricing ON TBLSTDPRICING
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON
-- Check for duplicate row. If there is no duplicate, do an insert.
IF (NOT EXISTS (SELECT P.[DATE]
      FROM TBLSTDPRICING P, inserted I
      WHERE P.[DATE] = I.[DATE] AND P.BANK = I.BANK AND 
            P.[APPLICATION] = I.APPLICATION AND 
            P.[PRODUCT TYPE] = I.[PRODUCT TYPE] AND 
            P.[PRICING REGION]= I.[PRICING REGION] AND 
            P.[SERVICE CODE] = I.[SERVICE CODE] AND 
            P.[EFFECTIVE DATE] = I.[EFFECTIVE DATE] <-- You didn't close the parens, add ))
   INSERT INTO TBLSTDPRICING
      SELECT I.* <-- and, chang to Inserted.* since I. was in the nested Select.
      FROM inserted
END

Other than those two things, I'd just run the above to get the new ones and
inserting into a different table, change the "Not Exists" to "Exists" and
store the duplicates for review in the other table.

hth,
Wayne

Pat Hartman · Sep 29, 2006

Thanks, I was three minutes ahead of you

The "Instead Of" trigger runs in place of the standard method for the event - insert/update/delete rather than in addition to it.

"Instead of" Insert trigger (1 Viewer)

Pat Hartman

Super Moderator

Pat Hartman

Super Moderator

WayneRyan

AWF VIP

Pat Hartman

Super Moderator

Similar threads

Users who are viewing this thread