SQL Server Trigger - Variable Table Name

Question

Is there anyway to update variable tables using SQL triggers?

I have one table called Readings that is updated with multiple different point numbers, I would like to have a trigger on this table that will read this point number and then update the specific table.

I have the following trigger that works, but the table name Z01 I would like to be a variable depending on the inserted data. Is this possible?

CREATE TRIGGER [dbo].[TRG_InsertTest] 
ON [dbo].[readings]
AFTER INSERT AS
BEGIN
INSERT INTO Z01 
SELECT * FROM INSERTED
END

I'm not sure this is possible because triggers need to be compiled down before they run in the background of your database. — Tim Biegeleisen
– Tim Biegeleisen, Commented Aug 21, 2017 at 6:43
This is usually a sign of a broken data model - that you have multiple tables with identical structure that you wish to treat generically. Usually you'll find that some of what should be modelled as data has instead been modelled as metadata. I.e. in a more normal data model, Z01 should be appearing as the data for (some column) in the single table that should be containing all of this data. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Aug 21, 2017 at 6:49
The issue is that we have multiple sensors which we get readings for every second so we do not want to combine them to one table, otherwise the table may become too large. The readings table is only updated sporadically when specific conditions are met. I have found a solution of having multiple triggers with where conditions but not sure if that is the best way to go. — mkb
– mkb, Commented Aug 21, 2017 at 6:57

Squirrel · Accepted Answer · 2017-08-21 07:25:23Z

here is some quick snippets to test out Dynamic SQL inside a trigger. Looks like it works !

-- create the tables
create table Readings
(
    id      int identity,
    tbl     varchar(10),
    some_val    int
)

create table Z01
(
    id      int identity,
    some_val    int
)

create table Z02
(
    id      int identity,
    some_val    int
)
go

-- create the insert trigger
CREATE TRIGGER [dbo].[TRG_InsertTest] 
ON [dbo].[Readings]
AFTER INSERT AS
BEGIN
    declare @sql    nvarchar(max)

    select  @sql    = isnull(@sql, '')
            + N'INSERT INTO '  + quotename(tbl) + ' (some_val) VALUES (' + convert(varchar(10), some_val) + ');'
    from    inserted

    exec    sp_executesql @sql
END

GO

-- some testing data
insert into Readings (tbl, some_val) 
select 'Z01', 10    union all
select 'Z01', 11    union all
select 'Z02', 20

select  *
from    Readings

select  *
from    Z01

select  *
from    Z02

Anagha · Accepted Answer · 2017-08-21 07:30:34Z

You can do like this -

 CREATE TRIGGER [dbo].[TRG_InsertTest] 
    ON [dbo].[readings]
    AFTER INSERT AS
    BEGIN
    DECLARE @a varchar(50)
    DECLARE @b int
    DECLARE @c datetime
    DECLARE @d varchar(20)

    SET NOCOUNT ON

    select  @a =  a  FROM INSERTED
    select  @b=  b FROM INSERTED
    select  @c =  c  FROM INSERTED
    select  @d =  d  FROM INSERTED

    If(@a = some condition and @b = some condition )
    begin
    INSERT INTO Z01 
    SELECT * FROM INSERTED
    end

    Else If(@a = some condition and @c = some condition )
    begin
    INSERT INTO Z02 
    SELECT * FROM INSERTED
    end

    Else If(@b = some condition and @d = some condition )
    begin
    INSERT INTO Z03
    SELECT * FROM INSERTED
    end

    Else
    begin
    INSERT INTO Z04
    SELECT * FROM INSERTED
    end
    END

Collectives™ on Stack Overflow

SQL Server Trigger - Variable Table Name

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related