Sql-server – How to create an INSERT/UPDATE/DELETE trigger

sql serversql-server-2012trigger

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?

Best Answer

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.

Related Solutions

MySQL – How to Disable Trigger for One Table

Actually, if you place an if then block in every trigger, you could effectively shutdown all triggers. Here is such a code block

IF @TRIGGER_DISABLED = 0 THEN
    ...trigger body
END IF;

In the mysql environment, you could

run SET @TRIGGER_DISABLED = 1;
do your data maintenance
run SET @TRIGGER_DISABLED = 0;

So your trigger for table A should look like this:

BEGIN 
    IF @TRIGGER_DISABLED = 0 THEN
    IF (OLD.status != 1 AND NEW.status = 2) THEN 
        IF (OLD.geo_lat IS NOT NULL AND OLD.geo_long IS NOT NULL) THEN 
            DELETE FROM geo WHERE datatype IN (3,4) AND foreignid = NEW.id; 
        END IF; 
    ELSEIF (OLD.Status = 1 AND NEW.Status != 2) THEN 
        IF (NEW.geo_lat IS NOT NULL AND NEW.geo_long IS NOT NULL) THEN 
            INSERT INTO geo (datatype, foreignid, long, lat, hostid, morton, status) VALUES  (IF(NEW.groupType=1,3,4), NEW.id, NEW.geo_long, NEW.geo_lat, NEW.hostid, 0, NEW.Status); 
        END IF; 
    ELSEIF (NEW.status != 3) THEN   
        IF (OLD.geo_lat IS NOT NULL AND OLD.geo_long IS NOT NULL AND (NEW.geo_lat IS NULL OR NEW.geo_long IS NULL)) THEN 
            DELETE FROM geo WHERE datatype IN (3,4) AND foreignid = NEW.id; 
        ElSEIF ((OLD.geo_lat IS NULL OR OLD.geo_long IS NULL) AND NEW.geo_lat IS NOT NULL AND NEW.geo_long IS NOT NULL) THEN 
            INSERT INTO geo (datatype, foreignid, longitude, latitude, hostid, morton, status) VALUES  (IF(NEW.groupType=1,3,4), NEW.id, NEW.geo_long, NEW.geo_lat, NEW.hostid, 0, NEW.Status); 
        ELSEIF (OLD.geo_lat!=NEW.geo_lat OR OLD.geo_long != NEW.geo_long OR OLD.status != NEW.status) THEN 
            UPDATE geo SET lat = NEW.geo_lat, long = NEW.geo_long, status = NEW.status WHERE datatype IN (3,4) AND foreignid = NEW.id; 
        END IF; 
    END IF; 
    END IF; 
END

So your trigger for table B should look like this:

CREATE TRIGGER `usergroups_comments_insert` AFTER INSERT ON `usergroups_comment`     
    FOR EACH ROW     
    BEGIN     
        IF @TRIGGER_DISABLED = 0 THEN
            CALL sp-set-comment_count(NEW.`gid`);     
        END IF;
    END;

If you want the triggers for table A to launch but not to table B, then add the code block only to table B's trigger.

Sql-server – Any reasons trigger AFTER INSERT would not fire

It is possible to disable a trigger. (The user could have disabled it) Also, it is possible that there are other triggers that return an error and rolls back the transaction, wich means the after trigger is not called.

Best Answer

Related Solutions

MySQL – How to Disable Trigger for One Table

Sql-server – Any reasons trigger AFTER INSERT would not fire

Related Question