在向表中添加新行后动态创建视图

Question

我有一个包含4个表的数据库

-battery -cell -owner

每个电池有多个电池和一个拥有者。

单元格表将包含大量条目。我想要一个触发器，为每个新电池创建视图，显示电池信息及其电池信息。

我有一个postgre sql触发器，它工作得很好(多亏了stackoverflow :)：

    BEGIN

EXECUTE $$CREATE OR REPLACE VIEW battery_vue
AS
SELECT * FROM cells WHERE battery_serial = $$ || NEW.battery_serial;

RETURN NEW;

END;

但我无法将其转置到SQL server上，而且我完全不知道如何做到这一点。

下面是我的数据库的简单示意图：

​

​

因此，在每个新电池的末尾，我需要一个它和它的电池的视图。

Ps :如果我的英语不流利，很抱歉:/

Answer 1

希望这能为你指明正确的方向……

   /*Create a test table*/
IF OBJECT_ID('TestBattery') IS NOT NULL
BEGIN
    DROP TABLE dbo.[TestBattery]
END
GO

CREATE TABLE dbo.[TestBattery]
(
id          INT
,owner_id    INT
,data_id     INT
)
GO

/*Create a test trigger*/
IF OBJECT_ID('trg_TestBattery_CreateView') IS NOT NULL
BEGIN
    DROP Trigger dbo.trg_TestBattery_CreateView
END
GO


CREATE TRIGGER [dbo].[trg_TestBattery_CreateView]
ON [dbo].[TestBattery] AFTER INSERT
AS
SET NOCOUNT ON 

DECLARE @BatteryID      INT
        ,@View          VARCHAR(200)
        ,@SQLCreateView VARCHAR(MAX)

SELECT @BatteryID = ID FROM INSERTED

/*Dynamically generate new view name based on BatteryID that was inserted*/
SELECT @View = 'TestBattery_'+CAST(@BatteryID AS VARCHAR)

/*Generate SQL to create the view*/
SELECT @SQLCreateView = 'CREATE VIEW '+@View+' AS SELECT [id],[owner_id],[data_id] FROM dbo.[TestBattery] WHERE ID = '+CAST(@BatteryID AS VARCHAR)

/*We create the view only if it doesn't exist*/
IF OBJECT_ID(@View) IS NULL
BEGIN

EXEC(@SQLCreateView)

END
GO

/*Testing your trigger*/
INSERT INTO TestBattery
(
id
,owner_id
,data_id
)
SELECT
'1'
,'2'
,'3'

SELECT * FROM TestBattery
SELECT * FROM TestBattery_1

/*Take out the trash*/
DROP TABLE TestBattery
DROP VIEW TestBattery_1