How to create an INSERT/UPDATE/DELETE trigger

Question

My knowledge with triggers is very poor.

I want to create an INSERT/UPDATE/DELETE trigger on a tableA with column names (A1,A2,A3,A4,A5,A6). After the triggers fires, I want to put that data in tableB with at least two different column names (B1, B2, A3, A4, A5, A6).

Basically if there is a change in tableA, that change should go in to tableB in the respective columns, i.e. A1->B1, A2->B2, A3->A3, like that.

Can anyone please help me with this if possible?

Answer 1

Here is a snippet of how you would construct the INSERT trigger. UPDATE would be very similar and DELETE would depend on how you want to handle it. We'll need a create table statement to help you in depth with all triggers.

Triggers are just T-SQL with a little logic tied in more or less. So how would you take data from A to B and rename it? Below is an example:

CREATE TABLE TableA (A1 INT
 , A2 INT
 , A3 INT
 , A4 INT
 , A5 INT
 , A6 INT);
CREATE TABLE TableB (B1 INT
 , B2 INT
 , A3 INT
 , A4 INT
 , A5 INT
 , A6 INT);
GO
CREATE TRIGGER TriggerTest
ON TableA
AFTER INSERT
AS
BEGIN
 INSERT INTO TableB (B1
 , B2
 , A3
 , A4
 , A5
 , A6)
 SELECT i.A1
 , i.A2
 , i.A3
 , i.A4
 , i.A5
 , i.A6
 FROM inserted i;
END;
GO

I'm matching up the column I want to INSERT with the SELECT clause, SQL knows A1 is going into B1 because of the ordering and declaration of the INSERT and SELECT. You can also use aliasing later if you need, example below:

SELECT 1 AS 'FakeNumber';
SELECT 1 AS 'NewColumnName';

Also, triggers can be both helpful and extremely dangerous. Use with caution. Put your development through the ringer with QA and testing, propagate up to production with caution. And as always, evaluate that you are using the best tool for the job, not all problems are nails and not all solutions are hammers.