USE [LIN2WORLD]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[user_marriage]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[user_marriage](
[char_id] [int] NOT NULL,
[partner_id] [int] NOT NULL,
[partner_classid] [int] NOT NULL,
[partner_race] [int] NOT NULL,
[partner_sex] [int] NOT NULL,
[married_date] [datetime] NOT NULL,
[married_type] [int] NOT NULL,
[partner_name] [nvarchar](25) NOT NULL
CONSTRAINT [PK_user_marriage] PRIMARY KEY CLUSTERED
(
[char_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]
ALTER TABLE [dbo].[user_marriage] ADD CONSTRAINT [DF_user_marriage_married_date] DEFAULT (getdate()) FOR [married_date]
END ELSE BEGIN
if columnproperty(object_id(N'[dbo].[user_marriage]'),N'married_type','AllowsNull') IS NULL
ALTER TABLE [dbo].[user_marriage] ADD [married_type] [int] NOT NULL
if columnproperty(object_id(N'[dbo].[user_marriage]'),N'partner_name','AllowsNull') IS NULL
ALTER TABLE [dbo].[user_marriage] ADD [partner_name] [nvarchar](25) NOT NULL
END
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[lin_MarryUser]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[lin_MarryUser]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[lin_DivorceUser]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[lin_DivorceUser]
GO
CREATE PROCEDURE [dbo].[lin_MarryUser]
@char_id [int],
@partner_name [nvarchar](25),
@partner_id [int],
@partner_classid [int],
@partner_race [int],
@partner_sex [int],
@married_type [int]
AS
BEGIN
IF EXISTS(SELECT * FROM [dbo].[user_marriage] WHERE [char_id] = @char_id AND [partner_id] = @partner_id)
BEGIN
UPDATE [dbo].[user_marriage]
SET [partner_classid] = @partner_classid
,[partner_race] = @partner_race
,[partner_sex] = @partner_sex
,[married_date] = GETDATE()
,[married_type] = @married_type
,[partner_name] = @partner_name
WHERE ([char_id] = @char_id) AND ([partner_id] = @partner_id)
END ELSE BEGIN
INSERT INTO [dbo].[user_marriage]
([char_id],[partner_id],[partner_classid],[partner_race],[partner_sex]
,[married_date],[married_type],[partner_name])
VALUES
(@char_id, @partner_id, @partner_classid, @partner_race, @partner_sex
,GETDATE(),@married_type,@partner_name)
END
END
GO
CREATE PROCEDURE [dbo].[lin_DivorceUser]
@char_id [int],
@partner_id [int]
AS
BEGIN
IF EXISTS(SELECT * FROM [dbo].[user_marriage] WHERE [char_id] = @char_id AND [partner_id] = @partner_id)
BEGIN
UPDATE [dbo].[user_marriage] SET [married_type] = 0
WHERE ([char_id] = @char_id) AND ([partner_id] = @partner_id)
END
END
GO