USE [MyDatabaseName]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[customer_trigger_INSERT]
ON [dbo].[Customer]
AFTER INSERT
AS

IF EXISTS (SELECT * FROM EmailContacts WHERE Email=(SELECT Email FROM inserted))
	BEGIN
		UPDATE EmailContacts
		SET Name = (SELECT FirstName + ' ' + LastName FROM inserted)
		WHERE Email = (SELECT Email FROM inserted)
	END
ELSE
	BEGIN
		INSERT INTO EmailContacts (Email, Name)
		SELECT FirstName + ' ' + LastName, Email FROM inserted
	END;

ALTER TRIGGER [dbo].[customer_trigger_INSERT]
ON [dbo].[Customer]
FOR INSERT
AS

UPDATE e
SET Name = i.FirstName + ' ' + i.LastName
FROM EmailContacts e
JOIN inserted i
ON e.Email = i.Email

IF @@ROWCOUNT = 0
	INSERT INTO EmailContacts (Email, Name)
	SELECT FirstName + ' ' + LastName, Email 
	FROM inserted

UPDATE e
SET Name = i.FirstName + ' ' + i.LastName
FROM EmailContacts e
    JOIN inserted i
        ON e.Email = i.Email
WHERE Name <> i.FirstName + ' ' + i.LastName

INSERT INTO EmailContacts (Email, Name)
SELECT Email, FirstName + ' ' + LastName
FROM inserted AS I
WHERE NOT EXISTS
(
    SELECT *
    FROM EmailContacts AS E
    WHERE E.Email = I.Email
)

NullIf(RTrim(COALESCE(FirstName + ' ', '') + COALESCE(LastName, '')), '')

ALTER TRIGGER [dbo].[customer_trigger_INSERT]
ON [dbo].[Customer]
FOR INSERT
AS

UPDATE e
SET Name = i.FirstName + ' ' + i.LastName
FROM EmailContacts e
    JOIN inserted i
        ON e.Email = i.Email
WHERE Name <> i.FirstName + ' ' + i.LastName

INSERT INTO EmailContacts (Email, Name)
SELECT Email, FirstName + ' ' + LastName
FROM inserted AS I
WHERE NOT EXISTS
(
    SELECT *
    FROM EmailContacts AS E
    WHERE E.Email = I.Email
)

BEGIN TRANSACTION

INSERT INTO dbo.Customer(FirstName, LastName, Email)
VALUES('Kristen', 'Netsirk', 'kristen@example.com')

SELECT TOP 10 * FROM dbo.Customer WHERE Email = 'kristen@example.com'

SELECT TOP 10 * FROM dbo. EmailContacts WHERE Email = 'kristen@example.com'

ROLLBACK

ALTER TRIGGER [dbo].[customer_trigger_INSERT]
ON [dbo].[Customer]
FOR INSERT
AS
BEGIN

UPDATE e
SET Name = i.FirstName + ' ' + i.LastName
FROM EmailContacts e
    JOIN inserted i
        ON e.Email = i.Email
WHERE Name <> i.FirstName + ' ' + i.LastName

INSERT INTO EmailContacts (Email, Name)
SELECT I.Email, I.FirstName + ' ' + I.LastName
FROM inserted AS I
LEFT JOIN EmailContacts AS E
    ON E.Email = I.Email
WHERE E.Email IS NULL
END

quote:

---

Originally posted by Kristen

"Could it be related to the fact that this gets triggered by a Stored Procedure?"

Only if the SProc (or some outer process) does a ROLLBACK. But then the changes would not be stored in your Customer table either.

Are you saying that if you drop this trigger you can use the Sproc to change a Customer record, but if you Create this trigger then you can NOT change a Customer Record with the Sproc (i.e. it rolls back)?

What happens if you do something like:

BEGIN TRANSACTION

INSERT INTO dbo.Customer(FirstName, LastName, Email)
VALUES('Kristen', 'Netsirk', 'kristen@example.com')

SELECT TOP 10 * FROM dbo.Customer WHERE Email = 'kristen@example.com'

SELECT TOP 10 * FROM dbo. EmailContacts WHERE Email = 'kristen@example.com'

ROLLBACK

this won't actually generate a row insert (because of the ROLLBACK), so you will either get an error (which will hopefully shed more light on the problem), or you will see something from the two SELECT statements (in which case the trigger is working fine, and there is indeed some issue with the SProc not liking your trigger)

---

ALTER proc [dbo].[aspdnsf_insCustomer]
    @Email nvarchar(100),
    @Password nvarchar(100),
    @SkinID int,
    @AffiliateID int,
    @Referrer ntext,
    @IsAdmin tinyint,
    @LastIPAddress varchar(40),
    @LocaleSetting nvarchar(10),
    @Over13Checked tinyint,
    @CurrencySetting nvarchar(10),
    @VATSetting int,
    @VATRegistrationID nvarchar(100),
    @CustomerLevelID int,
    @CustomerID int OUTPUT
 
AS
SET NOCOUNT ON
 

insert dbo.Customer(CustomerGUID, CustomerLevelID, RegisterDate, Email, Password, DateOfBirth, Gender, FirstName, LastName, Notes, SkinID, Phone, AffiliateID, Referrer, CouponCode, OkToEmail, IsAdmin, BillingEqualsShipping, LastIPAddress, OrderNotes, SubscriptionExpiresOn, RTShipRequest, RTShipResponse, OrderOptions, LocaleSetting, MicroPayBalance, RecurringShippingMethodID, RecurringShippingMethod, BillingAddressID, ShippingAddressID, GiftRegistryGUID, GiftRegistryIsAnonymous, GiftRegistryAllowSearchByOthers, GiftRegistryNickName, GiftRegistryHideShippingAddresses, CODCompanyCheckAllowed, CODNet30Allowed, ExtensionData, FinalizationData, Deleted, CreatedOn, Over13Checked, CurrencySetting, VATSetting, VATRegistrationID, StoreCCInDB, IsRegistered, LockedUntil, AdminCanViewCC, PwdChanged, BadLoginCount, LastBadLogin, Active, PwdChangeRequired, SaltKey)
values
(
    newid(),
    @CustomerLevelID,
    getdate(),
    isnull(@Email, ''),
    isnull(@Password, ''),
    null,
    null,
    null,
    null,
    null,
    isnull(@SkinID, 1),
    null,
    @AffiliateID,
    @Referrer,
    null,
    1,
    isnull(@IsAdmin, 0),
    0,
    @LastIPAddress,
    null,
    null,
    null,
    null,
    null,
    isnull(@LocaleSetting, ('en-US')),
    0.0,
    1,
    null,
    null,
    null,
    newid(),
    1,
    1,
    null,
    1,
    0,
    0,
    null,
    null,
    0,
    getdate(),
    @Over13Checked,
    @CurrencySetting,
    @VATSetting,
    @VATRegistrationID,
    1,
    0,
    null,
    1,
    getdate(),
    0,
    null,
    1,
    0,
    0
)
 
set @CustomerID = @@identity

set @CustomerID = SCOPE_IDENTITY( )