Update 2 databases with 1 script

  • SQL_ME_RICH

    SSChampion

    Points: 11200

    Comments posted to this topic are about the item Update 2 databases with 1 script

  • SQL_ME_RICH

    SSChampion

    Points: 11200

    My apologies on the way this got published and formatted. I was trying to get some additional DDL put into the publication, and for some reason I thought that this "SQL Scripts Manager" was the way to do this (from other scripts that I have seen published out here in the past).

    Here are the 2 tables and the 1 DML trigger needed in addition to the script. Please again - feel free to modify as you need, and definitely improve as you wish!

    VERSION Table

    /****** Object: Table [dbo].[VERSION] Script Date: 03/03/2011 12:12:12 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_PADDING ON

    GO

    CREATE TABLE [dbo].[VERSION](

    [TheKey] [nvarchar](20) NOT NULL,

    [TheValue] [nvarchar](10) NOT NULL,

    [TheTextValue] [nvarchar](10) NOT NULL,

    CONSTRAINT [PK_TheKey] PRIMARY KEY CLUSTERED

    (

    [TheKey] ASC,

    [TheValue] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY]

    GO

    VERSION_HISTORY Table

    /****** Object: Table [dbo].[VERSION_HISTORY] Script Date: 03/02/2011 12:12:12 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_PADDING ON

    GO

    CREATE TABLE [dbo].[VERSION_HISTORY](

    [ID] [int] IDENTITY(1,1) NOT NULL,

    [TheKey] [nvarchar](20) NOT NULL,

    [LastUpdate] [datetime] NOT NULL CONSTRAINT [DF_VERSION_HISTORY_LastUpdate] DEFAULT (getdate()),

    [TheValue] [nvarchar](10) NOT NULL,

    [TheTextValue] [nvarchar](10) NOT NULL,

    CONSTRAINT [PK_ID4] PRIMARY KEY CLUSTERED

    (

    [ID] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY]

    GO

    VH-INSERT Trigger

    --Creates a Trigger for the VERSION table so that upon an update, the VERSION_HISTORY table receives the info.

    IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[Trg_VH-INSERT]'))

    DROP TRIGGER [dbo].[Trg_VH-INSERT]

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    --Create Trigger

    CREATE TRIGGER [dbo].[Trg_VH-INSERT]

    ON [dbo].[VERSION]

    AFTER UPDATE

    AS

    BEGIN

    --Creates new VERSION_HISTORY table record when an UPDATE takes place on the VERSION table. ~RWY 2/23/2011

    INSERT INTO dbo.VERSION_HISTORY ([TheKey],[TheValue],[TheTextValue])

    SELECT TheKey, TheValue, TheTextValue

    FROM inserted

    WHERE TheTextValue IS NOT NULL

    END

    GO

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the script.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply