DataBase [member테이블, 회원가입프로시져, 회원가입 트리거]

--테이블 생성
create table member
(
 mID  varchar(20) not null primary key,
 mPwd varchar(20) not null,
 mName nvarchar(5) not null,
 mNickName varchar(20) not null,
 mEmail varchar(50) not null,
 mHomepage varchar(50),
 mBlog varchar(50),
 mBirthday char(10),
 mMailling bit default 0,
 mRegDate datetime default getdate(),
 mVisit int default 0,
 mLastVisitDate datetime default getdate()
)

--프로시져
create proc memberInsert
(
 @mID varchar(20),
 @mPwd varchar(20),
 @mName nvarchar(5),
 @mNickName varchar(20),
 @mEmail varchar(50),
 @mHomepage varchar(50),
 @mBlog varchar(50),
 @mBirthday char(10),
 @mMailling bit
)
as
begin
 insert into member (mID, mPwd, mName, mNickName, mEmail, mHomepage, mBlog, mBirthday, mMailling)
 values (@mID, @mPwd, @mName, @mNickName, @mEmail, @mHomepage, @mBlog, @mBirthday, @mMailling)
end


-- 트리거생성
-- 트리거는 중복아이디, 중복닉네임, 중복메일을 체크한후 해당 데이터의 위치에 1의 값을 반환한다.
-- 000 : 정상, 100 : 아이디 중복, 010 : 닉네임 중복, 001 : 메일주소 중복(110, 101, 111 등으로 반환된다.)
create trigger TRG_MEMBER_INS
on member
instead of insert
as
begin
 set nocount on;
 begin tran
 begin try
  declare @getID   varchar(20)
  declare @getNickName varchar(20)
  declare @getEmail  varchar(50)
  declare @errCode  char(3)
  set @errCode = '000'

  select @getID = mID, @getNickName = mNickName, @getEmail = mEmail
  from inserted

  --중복된 아이디 검사
  if(exists(select * from member where mID=@getID))
  begin
   set @errCode = '1' + substring(@errCode, 2, 2)
  end

  --중복된 닉네임 검사  
  if(exists(select * from member where mNickName = @getNickName))
  begin
   set @errCode = substring(@errCode,1,1) + '1' + substring(@errCode, 3, 1)
  end

  --중복된 메일주소 검사
  if(exists(select * from member where mEmail = @getEmail))
  begin
   set @errCode = substring(@errCode, 1, 2) + '1'
  end

  if(@errCode = '000')
  begin
   insert into member(mID, mPwd, mName, mNickName, mEmail, mHomepage, mBlog, mBirthday, mMailling)
   select mID, mPwd, mName, mNickName, mEmail, mHomepage, mBlog, mBirthday, mMailling from inserted
  end

 end try
 begin catch
  if @@TRANCOUNT > 0
   ROLLBACK TRAN
 end catch

 if @@TRANCOUNT > 0
  COMMIT TRAN
 
 select @errCode as errCode
end