Category: SQL Azure Trigger

Postgres / SQL Server / MariaDB – Table Setup to Store Persistent Default Values

In Postgres and a number of other databases you cannot directly set a columns’ default value to come from another table. Here I talk through how you can setup another table and use a trigger to pull the value from that table and insert it as a default value into a field of your choice if on Insert the value is null.

Postgres

-- Parent table
CREATE TABLE t002defaultvalues (
    gid bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    warehouse text
);

-- Stock table
CREATE TABLE t001stock (
    gid bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    warehouse text
);

-- Function to fetch default warehouse
CREATE OR REPLACE FUNCTION set_default_warehouse()
RETURNS trigger AS $$
BEGIN
    IF NEW.warehouse IS NULL THEN
        SELECT warehouse
        INTO NEW.warehouse
        FROM t002defaultvalues
        WHERE gid = 1;  -- adjust if you want another rule
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Trigger to apply the function
CREATE TRIGGER trg_t001stock_default_warehouse
BEFORE INSERT ON t001stock
FOR EACH ROW
EXECUTE FUNCTION set_default_warehouse();

SQL Server (T-SQL)
In SQL Server, defaults can’t reference other tables, so an AFTER INSERT trigger updates only the newly inserted rows (via the inserted pseudo-table).

-- Parent table
CREATE TABLE dbo.t002defaultvalues (
    gid BIGINT IDENTITY(1,1) PRIMARY KEY,
    warehouse NVARCHAR(MAX)  -- TEXT is deprecated in SQL Server
);

-- Stock table
CREATE TABLE dbo.t001stock (
    gid BIGINT IDENTITY(1,1) PRIMARY KEY,
    warehouse NVARCHAR(MAX) NULL
);
GO

-- Trigger: fill warehouse from t002defaultvalues when NULL
CREATE TRIGGER dbo.trg_t001stock_default_warehouse
ON dbo.t001stock
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE s
    SET s.warehouse = dv.warehouse
    FROM dbo.t001stock AS s
    INNER JOIN inserted AS i
        ON s.gid = i.gid
    CROSS APPLY (
        SELECT TOP (1) warehouse
        FROM dbo.t002defaultvalues
        WHERE gid = 1      -- adjust rule if needed
    ) AS dv
    WHERE s.warehouse IS NULL;
END;
GO

-- Example
INSERT INTO dbo.t002defaultvalues (warehouse) VALUES (N'Main Depot');
INSERT INTO dbo.t001stock (warehouse) VALUES (NULL); -- will be set by trigger

MariaDB
In MariaDB, a BEFORE INSERT trigger can assign NEW.warehouse from a scalar subquery.

-- Parent table
CREATE TABLE t002defaultvalues (
    gid BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    warehouse TEXT
);

-- Stock table
CREATE TABLE t001stock (
    gid BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    warehouse TEXT NULL
);

DELIMITER $$

-- Trigger: fill warehouse from t002defaultvalues when NULL
CREATE TRIGGER trg_t001stock_default_warehouse
BEFORE INSERT ON t001stock
FOR EACH ROW
BEGIN
    IF NEW.warehouse IS NULL THEN
        SET NEW.warehouse = (
            SELECT warehouse
            FROM t002defaultvalues
            WHERE gid = 1    -- adjust rule if needed
            LIMIT 1
        );
    END IF;
END$$

DELIMITER ;

-- Example
INSERT INTO t002defaultvalues (warehouse) VALUES ('Main Depot');
INSERT INTO t001stock () VALUES (); -- will be set by trigger

Adjust WHERE gid = 1 to whatever selection rule you need (e.g., ORDER BY created_at DESC LIMIT 1, or WHERE active = 1 LIMIT 1).

SQL Azure – Using a Trigger to watch changes on fields

The below trigger monitors a table called t001Table4Monitoring and four particular fields / 3 date fields and one integer field. It monitors the trigger table and compares new values to old values using simple subtraction. As a comparison between null and a value will always return null we need to catch situations were a field is changed from null or to null hence the ISNULL section. The record has a bit field that is updated to True if change is detected.

This code was created with the help of GPT-4 

CREATE TRIGGER [dbo].[trg_Update_t001Table4Monitoring]
ON [dbo].[t001Table4Monitoring]
AFTER UPDATE
AS
BEGIN
    IF UPDATE(Date01Monitor) OR UPDATE(Date02Monitor) OR UPDATE(Date03Monitor) OR UPDATE(IntegerMonitor)
    BEGIN
        -- Get old and new values
        DECLARE @old_Date01Monitor DATE, @new_Date01Monitor DATE;
        DECLARE @old_Date02Monitor DATE, @new_Date02Monitor DATE;
        DECLARE @old_Date03Monitor DATE, @new_Date03Monitor DATE;
        DECLARE @old_IntegerMonitor INT, @new_IntegerMonitor INT;

        SELECT @old_Date01Monitor = Date01Monitor, @old_Date02Monitor = Date02Monitor, @old_Date03Monitor = Date03Monitor, @old_IntegerMonitor = IntegerMonitor FROM deleted;
        SELECT @new_Date01Monitor = Date01Monitor, @new_Date02Monitor = Date02Monitor, @new_Date03Monitor = Date03Monitor, @new_IntegerMonitor = IntegerMonitor FROM inserted;

        -- Check if values have changed
        IF (ISNULL(@old_Date01Monitor, '1900-01-01') <> ISNULL(@new_Date01Monitor, '1900-01-01')) OR 
           (ISNULL(@old_Date02Monitor, '1900-01-01') <> ISNULL(@new_Date02Monitor, '1900-01-01')) OR 
           (ISNULL(@old_Date03Monitor, '1900-01-01') <> ISNULL(@new_Date03Monitor, '1900-01-01')) OR 
           (ISNULL(@old_IntegerMonitor, 0) <> ISNULL(@new_IntegerMonitor, 0))
        BEGIN
            UPDATE dbo.t001Table4Monitoring
            SET RecordChanged = 1
            FROM dbo.t001Table4Monitoring t
            INNER JOIN inserted i ON t.pkid = i.pkid
        END
    END
END;