Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Update 2 databases with 1 script Expand / Collapse
Author
Message
Posted Saturday, March 12, 2011 11:32 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, December 15, 2014 4:48 PM
Points: 485, Visits: 1,373
Comments posted to this topic are about the item Update 2 databases with 1 script
Post #1077342
Posted Sunday, March 13, 2011 12:19 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, December 15, 2014 4:48 PM
Points: 485, Visits: 1,373
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

Post #1077481
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse