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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy