CODEWITHSUNDEEP
sql-servert-sqltriggers
Eleuis
Eleuis

Reputation: 37

Inside a trigger, insert n rows from the original table into a new table

I have the following table:

==OFFICES==
OFFICE INTEGER NOT NULL,
CITY VARCHAR(15) NOT NULL,
REGION VARCHAR(15) NOT NULL,
DIR INTEGER,
OBJECTIVE MONEY,
SALES MONEY

I have to define a trigger named TRIGGER_EX3. Whenever a new row is inserted into OFFICES, it stores into a table named TCONTROL_OFFICES the following values:

==TCONTROL_OFFICES==
INSERTION_DATE DATETIME, --The day the office was inserted
OPERATION VARCHAR(15), --The name of the operation (inserted)
OFFICE INTEGER, --The id of the office (The value of OFFICE in OFFICES)
THE_USER VARCHAR(30) --Name of the user

My idea was to use a while loop (or for loop) to insert the first row of INSERTED into TCONTROL_OFFICES and then delete that same row from INSERTED and repeat until INSERTED was empty. However, it seems I can't modify INSERTED. Regardless, it still seemed like a clumsy approach. Is there any better way of doing this?

This is what I have so far:

USE EMPLOYEES
GO
IF(OBJECT_ID ('TRIGGER_EX3', 'TR') IS NOT NULL)
BEGIN
    DROP TRIGGER TRIGGER_EX3;
END
GO
CREATE TRIGGER TRIGGER_EX3 ON OFFICES
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON
    --CHECK IF THE TABLE EXISTS
    IF EXISTS (SELECT * 
        FROM SYS.TABLES
        WHERE NAME = 'TCONTROL_OFFICES') --IT DOES
    BEGIN
        PRINT 'IT EXISTS';
    END
    ELSE --IT DOESNT
    BEGIN
        CREATE TABLE TCONTROL_OFFICES
        (INSERTION_DATE DATETIME,
        OPERATION VARCHAR(15),
        OFFICE INTEGER,
        THE_USER VARCHAR(30));
    END

    WHILE((SELECT COUNT(*) FROM INSERTED) > 0)
    BEGIN
        INSERT INTO TCONTROL_OFFICES
        VALUES(GETDATE(), 'INSERT', (SELECT OFFICE FROM INSERTED), SUSER_SNAME())

        DELETE TOP (1) FROM INSERTED
    END
END

Keep in mind that the insertion can be of 3 rows but it may also be of 100+ so I cannot write 1 by 1 every row I want to insert.

Upvotes: 0

Views: 62

Answers (1)

sticky bit
sticky bit

Reputation: 37527

Simply do an INSERT ... SELECT. You don't need any loop at all.

INSERT INTO tcontrol_offices
            (insertion_date,
             operation,
             office,
             the_user)
            SELECT getdate(),
                   'INSERT',
                   office,
                   suser_sname()
                   FROM inserted;

Upvotes: 1

Related Questions