March 12, 2011 at 11:32 am
Comments posted to this topic are about the item Update 2 databases with 1 script
March 13, 2011 at 12:19 pm
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
May 17, 2016 at 6:38 am
Thanks for the script.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply