Create SQL Trigger to update table

I have two tables (1) -tblARCustomer and (2) -tblSalesLead. I have a third table (3) -tblEmailCentral that contains the CompanyName and EmailAddress. I need a trigger for table 1 and 2 that will update table 3 with the CompanyName and EmailAddress anytime a new EmailAddress is added or updated in table 1 or 2.

I new at SQL triggers - Please help.
LMezzettaAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
YiogiConnect With a Mentor Commented:
And your update trigger. SQL server does not support a BEFORE UPDATE trigger. And if you do an after insert trigger you won't know the old email address so that you can compare it with the 3rd table and see if something changed. So the only way you can do this is use an INSTEAD OF UPDATE trigger instead.

So here it goes. I assume you won't need to insert any records since they should all already be inserted from the previous trigger. Now of course you can add this in your code but I'll let you do some thinking of your own. It's not hard and here is a hint. You can have both an INSTEAD OF and an AFTER trigger and they will be executed in that sequence :). And a second hint you can simply create a trigger for both AFTER INSERT, UPDATE. Well I virtually gave the solution away.

Here is your INSTEAD OF UPDATE TRIGGER. Btw I'm using the email address to do the checking between tables 1 and 3. You can use both email and name or just name or whatever you like.

CREATE TRIGGER [dbo].[INSTEADOFUPDATETRIGGER_tblARCustomer] ON tblARCustomer
INSTEAD OF UPDATE
AS
SET NOCOUNT ON
 
-- First update EmailCentral
UPDATE [dbo].[tblEmailCentral]
       SET tblEmailCentral.EmailAddress = INSERTED.EmailAddress,
           tblEmailCentral.CompanyName = INSERTED.CompanyName 
       FROM INSERTED
      INNER JOIN tblARCustomer
            ON tblARCustomer.ID = INSERTED.ID
            WHERE tblARCustomer.EmailAddress = [tblEmailCentral].EmailAddress
 
-- And now do your instead of update. Simply update based on the id.
UPDATE [dbo].[tblARCustomer.EmailAddress ]
       SET tblARCustomer.EmailAddress = INSERTED.EmailAddress,
           tblARCustomer.CompanyName = INSERTED.CompanyName 
       FROM INSERTED
            WHERE tblARCustomer.ID = INSERTED.ID
GO

Open in new window

0
 
YiogiCommented:
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

Open in new window

0
 
YiogiCommented:
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

Open in new window

0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
LMezzettaAuthor Commented:
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
0
 
YiogiCommented:
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?
0
 
LMezzettaAuthor Commented:
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.
0
 
YiogiCommented:
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

Open in new window

0
 
LMezzettaAuthor Commented:
Thank you!
0
All Courses

From novice to tech pro — start learning today.