Need Urgent help with trigger - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Need Urgent help with trigger

Author

Topic

tobtob
Starting Member

7 Posts

Posted - 2010-12-14 : 17:34:00

I'm very new to SQL in general and a complete novice with triggers but here's what going on. Can anyone help explain what is happening and how I can prevent it?
I'm using a INSTEAD OF INSERT trigger to insert a record into identical tables in 2 different databases. I used INSTEAD OF INSERT because of an error message I was getting that would require a change of a datatype.
Anyway..... The problem is that 3 separate times the record was not inserted into the first table but it was inserted into the second table. This has created all sorts of problems. Any help would be appreciated.
Here is the trigger code... It's a little long .
----------------------------------------------------
USE [MarksSSPRO]
GO
/****** Object: Trigger [dbo].[Copy_New_User_to_TeeTimeCouponsDB] Script Date: 12/14/2010 17:12:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[Copy_New_User_to_TeeTimeCouponsDB] ON
[dbo].[sitestorepro_users]
INSTEAD OF INSERT
AS
INSERT INTO [dbo].[sitestorepro_users]
(
Salutation, FirstName, MiddleName, LastName, CompanyName, ShippingAddress1, ShippingAddress2, ShippingCity, ShippingCountry, ShippingState, ShippingRegion, ShippingZipCode, PhoneNumber1, PhoneNumber2, FaxNumber, BirthDate_SingleField, BillingCountry, BillingAddress1, BillingAddress2, BillingCity, BillingState, BillingRegion, BillingZipCode, UserEmail, Receive_Emails, UserPassword, OptionalField1, OptionalField2, OptionalField3, OptionalField4, OptionalField5, OptionalField6, OptionalField7, OptionalField8, OptionalField9, OptionalField10, OptionalField11, OptionalField12, OptionalField13, OptionalField14, OptionalField15, OptionalField16, OptionalField17, OptionalField18, OptionalField19, OptionalField20, OptionalNumeric1, OptionalNumeric2, OptionalNumeric3, OptionalNumeric4, OptionalNumeric5, OptionalListMenu1, OptionalListMenu2, OptionalListMenu3, OptionalListMenu4, OptionalListMenu5, OptionalRadioGroup1, OptionalRadioGroup2, OptionalRadioGroup3, OptionalRadioGroup4, OptionalRadioGroup5, OptionalCheckBox1, OptionalCheckBox2, OptionalCheckBox3, OptionalCheckBox4, OptionalCheckBox5, CustomerType, RegisterContentID, RegistrationToken1, RegistrationToken2, RegistrationDate, sitestorepro_upgrade_text1, sitestorepro_upgrade_text2, BillingCompany)
SELECT
Salutation, FirstName, MiddleName, LastName, CompanyName, ShippingAddress1, ShippingAddress2, ShippingCity, ShippingCountry, ShippingState, ShippingRegion, ShippingZipCode, PhoneNumber1, PhoneNumber2, FaxNumber, BirthDate_SingleField, BillingCountry, BillingAddress1, BillingAddress2, BillingCity, BillingState, BillingRegion, BillingZipCode, UserEmail, Receive_Emails, UserPassword, OptionalField1, OptionalField2, OptionalField3, OptionalField4, OptionalField5, OptionalField6, OptionalField7, OptionalField8, OptionalField9, OptionalField10, OptionalField11, OptionalField12, OptionalField13, OptionalField14, OptionalField15, OptionalField16, OptionalField17, OptionalField18, OptionalField19, OptionalField20, OptionalNumeric1, OptionalNumeric2, OptionalNumeric3, OptionalNumeric4, OptionalNumeric5, OptionalListMenu1, OptionalListMenu2, OptionalListMenu3, OptionalListMenu4, OptionalListMenu5, OptionalRadioGroup1, OptionalRadioGroup2, OptionalRadioGroup3, OptionalRadioGroup4, OptionalRadioGroup5, OptionalCheckBox1, OptionalCheckBox2, OptionalCheckBox3, OptionalCheckBox4, OptionalCheckBox5, CustomerType, RegisterContentID, RegistrationToken1, RegistrationToken2, RegistrationDate, sitestorepro_upgrade_text1, sitestorepro_upgrade_text2, BillingCompany
FROM inserted
INSERT INTO [teetimecoupon].[dbo].[sitestorepro_users]
(
Salutation, FirstName, MiddleName, LastName, CompanyName, ShippingAddress1, ShippingAddress2, ShippingCity, ShippingCountry, ShippingState, ShippingRegion, ShippingZipCode, PhoneNumber1, PhoneNumber2, FaxNumber, BirthDate_SingleField, BillingCountry, BillingAddress1, BillingAddress2, BillingCity, BillingState, BillingRegion, BillingZipCode, UserEmail, Receive_Emails, UserPassword, OptionalField1, OptionalField2, OptionalField3, OptionalField4, OptionalField5, OptionalField6, OptionalField7, OptionalField8, OptionalField9, OptionalField10, OptionalField11, OptionalField12, OptionalField13, OptionalField14, OptionalField15, OptionalField16, OptionalField17, OptionalField18, OptionalField19, OptionalField20, OptionalNumeric1, OptionalNumeric2, OptionalNumeric3, OptionalNumeric4, OptionalNumeric5, OptionalListMenu1, OptionalListMenu2, OptionalListMenu3, OptionalListMenu4, OptionalListMenu5, OptionalRadioGroup1, OptionalRadioGroup2, OptionalRadioGroup3, OptionalRadioGroup4, OptionalRadioGroup5, OptionalCheckBox1, OptionalCheckBox2, OptionalCheckBox3, OptionalCheckBox4, OptionalCheckBox5, CustomerType, RegisterContentID, RegistrationToken1, RegistrationToken2, RegistrationDate, sitestorepro_upgrade_text1, sitestorepro_upgrade_text2, BillingCompany, CustomerVerified)
SELECT
Salutation, FirstName, MiddleName, LastName, CompanyName, ShippingAddress1, ShippingAddress2, ShippingCity, ShippingCountry, ShippingState, ShippingRegion, ShippingZipCode, PhoneNumber1, PhoneNumber2, FaxNumber, BirthDate_SingleField, BillingCountry, BillingAddress1, BillingAddress2, BillingCity, BillingState, BillingRegion, BillingZipCode, UserEmail, Receive_Emails, UserPassword, OptionalField1, OptionalField2, OptionalField3, OptionalField4, OptionalField5, OptionalField6, OptionalField7, OptionalField8, OptionalField9, OptionalField10, OptionalField11, OptionalField12, OptionalField13, OptionalField14, OptionalField15, OptionalField16, OptionalField17, OptionalField18, OptionalField19, OptionalField20, OptionalNumeric1, OptionalNumeric2, OptionalNumeric3, OptionalNumeric4, OptionalNumeric5, OptionalListMenu1, OptionalListMenu2, OptionalListMenu3, OptionalListMenu4, OptionalListMenu5, OptionalRadioGroup1, OptionalRadioGroup2, OptionalRadioGroup3, OptionalRadioGroup4, OptionalRadioGroup5, OptionalCheckBox1, OptionalCheckBox2, OptionalCheckBox3, OptionalCheckBox4, OptionalCheckBox5, CustomerType, RegisterContentID, RegistrationToken1, RegistrationToken2, RegistrationDate, sitestorepro_upgrade_text1, sitestorepro_upgrade_text2, BillingCompany, '1'
FROM inserted
------------------------------------------------------------------

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-12-14 : 22:18:23

It is just plain insert statement from inserted table. I don't see any issue in this.

Check whether any trigger exists on table :[dbo].[sitestorepro_users]

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2010-12-14 : 22:52:57

I am asking the following because you have the SAME table name in both databases:

Is it possible that some of the connections (or users) executed the insert command from the teetimecoupon database rather than MarksSSPRO? This can happen, for example, if a user's default database is teetimecoupon and they sent the insert statement without specifying the database, or they executed the insert statement from SSMS while teetimecoupon was the selected database.

tobtob
Starting Member

7 Posts

Posted - 2010-12-14 : 23:07:10

quote:
Originally posted by pk_bohra

It is just plain insert statement from inserted table. I don't see any issue in this.

Check whether any trigger exists on table :[dbo].[sitestorepro_users]

Yes, This is the trigger that exists.

tobtob
Starting Member

7 Posts

Posted - 2010-12-14 : 23:17:20

quote:
Originally posted by sunitabeck

I am asking the following because you have the SAME table name in both databases:

Is it possible that some of the connections (or users) executed the insert command from the teetimecoupon database rather than MarksSSPRO? This can happen, for example, if a user's default database is teetimecoupon and they sent the insert statement without specifying the database, or they executed the insert statement from SSMS while teetimecoupon was the selected database.

The inserts are done as a result of registering for an account on the site that is accessing MarksSSPRO database. I copy insert the new user record into MarksSSPRO db and into the same tablename on the teetimecoupon site. This gives the user access to 2 websites after registering on the first site.
I removed the ability to trgister on the second site. So there is no way that anyone could have inserted into the teetimecoupon db.

Is there any error checking that should be done to make sure both inserts are successful when working with a trigger like this ?

Kristen
Test

22859 Posts

Posted - 2010-12-15 : 04:34:16

You could put an explicit BEGIN TRANSACTION at the start, and check that the @@ROWCOUNT after each INSERT is the same as the COUNT(*) in INSERTED (and if not ROLLBACK, if both INSERT Rowcounts are correct then, and only then, COMMIT)

"The problem is that 3 separate times the record was not inserted into the first table but it was inserted into the second table."

If you have users who have INSERT permission for the [teetimecoupon].[dbo].[sitestorepro_users] table then all bets are off!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources