Create SQL Trigger to update table

Your two triggers for one table below. I believe you can do for the second yourself, same reasoning.

CREATE TRIGGER [dbo].[AFTERINSERTTRIGGER_tblARCustomer] ON dbo.tblARCustomer
AFTER INSERT
AS
SET NOCOUNT ON
UPDATE [dbo].[tblEmailCentral]
       SET tblEmailCentral.EmailAddress = INSERTED.EmailAddress,
           tblEmailCentral.CompanyName = INSERTED.CompanyName 
           FROM INSERTED
                WHERE [tblEmailCentral].YourCommonKey = INSERTED.YourCommonKey AND [tblEmailCentral].OptionallySecondColumnToJoinOn = INSERTED.OptionallySecondColumnToJoinOn
GO
 
CREATE TRIGGER [dbo].[AFTERUPDATETRIGGER_tblARCustomer] ON tblARCustomer
AFTER UPDATE
AS
SET NOCOUNT ON
UPDATE [dbo].[tblARCustomer]
       SET TEMPW_DateTimeModified = GetDate(),
           TEMPW_WorkDate = dbo.udf_GetDateOnly(B.[TEMPW_DateTimeIn])
           FROM INSERTED
       SET tblARCustomer.EmailAddress = INSERTED.EmailAddress,
           tblARCustomer.CompanyName = INSERTED.CompanyName 
           FROM INSERTED
                WHERE [tblEmailCentral].YourCommonKey = INSERTED.YourCommonKey AND [tblEmailCentral].OptionallySecondColumnToJoinOn = INSERTED.OptionallySecondColumnToJoinOn
GO

Select allOpen in new window

Bah sorry forgot to delete my own code lines. Here this will work:

CREATE TRIGGER [dbo].[AFTERINSERTTRIGGER_tblARCustomer] ON dbo.tblARCustomer
AFTER INSERT
AS
SET NOCOUNT ON
UPDATE [dbo].[tblEmailCentral]
       SET tblEmailCentral.EmailAddress = INSERTED.EmailAddress,
           tblEmailCentral.CompanyName = INSERTED.CompanyName 
           FROM INSERTED
                WHERE [tblEmailCentral].YourCommonKey = INSERTED.YourCommonKey AND [tblEmailCentral].OptionallySecondColumnToJoinOn = INSERTED.OptionallySecondColumnToJoinOn
GO
 
CREATE TRIGGER [dbo].[AFTERUPDATETRIGGER_tblARCustomer] ON tblARCustomer
AFTER UPDATE
AS
SET NOCOUNT ON
UPDATE [dbo].[tblARCustomer]
       SET tblARCustomer.EmailAddress = INSERTED.EmailAddress,
           tblARCustomer.CompanyName = INSERTED.CompanyName 
           FROM INSERTED
                WHERE [tblEmailCentral].YourCommonKey = INSERTED.YourCommonKey AND [tblEmailCentral].OptionallySecondColumnToJoinOn = INSERTED.OptionallySecondColumnToJoinOn
GO

Select allOpen in new window

Thanks.. But I think I need to go one step more.

I want the trigger to insert the EmailAddress and CompanyName into the table EmailCentral if it is not already there. Then if the record is updated in the ARCustomer table or the SalesLead table the update trigger will fire and update the EmailCentral table. I do not have any link fields between the EmailCentral and ARCustoner or SalesLead table. The EmailCentral table on has in ID field, CompanyName field and the EmailAddress field.

Thanks

Do the ARCustomer and SalesLeadTables have IDs, even if they are not linked in any way? And I don't mean the name or email since those can be updated, but an id that cannot be updated or replaced in any way so in case of an update I can at least match the record being updated with the original one?

Yiogi:

Yes, they do. The field name is intID.  The table EmailCentral also has the same intID which is a unique ID (auto number) field.

Ok so what you need to do is check the emailcentral table if the email address being inserted exists. If it does you want add it. You surely won't doing any updates on new records and even if you want to there is no way of determining that the new address being inserted is actually an update of an old one in a different table.

Again I'm providing it for one of the tables it's not hard at all to change the name and do it for the second. Basically what you have in your trigger is pure T-SQL. So you could plug in your reasoning yourself but here it is below. I will make a second post for update trigger just stay on hold. If you have already created the trigger then please change the first word (CREATE) with ALTER before running it. Alternatively drop it and recreate it.

CREATE TRIGGER [dbo].[AFTERINSERTTRIGGER_tblARCustomer] ON dbo.tblARCustomer
AFTER INSERT
AS
SET NOCOUNT ON
INSERT INTO [dbo].[tblEmailCentral]
            (CompanyName, EmailAddress) 
     SELECT INSERTED.EmailAddress,
            INSERTED.CompanyName
            WHERE INSERTED.EmailAddress
                  NOT IN (SELECT EmailAddress FROM [dbo].[tblEmailCentral])
GO

Select allOpen in new window

Thank you!