1

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?

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.