sql2.0.txt

CREATE TABLE [dbo].[article] (
    [article_id]    INT          IDENTITY (1, 1) NOT NULL,
    [user_id]       INT          NULL,
    [reward]        INT          NULL,
    [article]       TEXT         NULL,
    [article_title] VARCHAR (20) NULL,
    CONSTRAINT [PK_article] PRIMARY KEY CLUSTERED ([article_id] ASC)
);
/*
    [article_id]    自增 不填
    [user_id]      文章作者
    [reward]     文章收到的打赏
    [article]      文章
    [article_title] 文章标题
*/


CREATE TABLE [dbo].[attention] (
    [un_id]     INT IDENTITY (1, 1) NOT NULL,
    [p_user_id] INT NULL,
    [a_user_id] INT NULL,
    CONSTRAINT [PK_attention] PRIMARY KEY CLUSTERED ([un_id] ASC)
);
/*
    [un_id]     自增 不填
    [p_user_id]   被关注的用户id
    [a_user_id]   主动关注的用户id

*/


CREATE TABLE [dbo].[checkin] (
    [un_id]      INT      IDENTITY (1, 1) NOT NULL,
    [user_id]    INT      NULL,
    [last_login] DATETIME NULL,
    [continuous] INT      NULL,
    CONSTRAINT [PK_checkin] PRIMARY KEY CLUSTERED ([un_id] ASC)
);
/*
    [un_id]     自增 不填
    [user_id]    签到的用户
    [last_login] 上次签到时间
    [continuous] 连续日期

*/



CREATE TABLE [dbo].[collect] (
    [un_id]      INT IDENTITY (1, 1) NOT NULL,
    [user_id]    INT NULL,
    [article_id] INT NULL,
    CONSTRAINT [PK_collect] PRIMARY KEY CLUSTERED ([un_id] ASC)
);
/*
    [un_id]      自增  不填
    [user_id]    主动收藏的用户id
    [article_id]   收藏的文章id


*/



CREATE TABLE [dbo].[message] (
    [un_id]      INT          IDENTITY (1, 1) NOT NULL,
    [user_id]    INT          NULL,
    [article_id] INT          NULL,
    [message]    VARCHAR (20) NULL,
    CONSTRAINT [PK_message] PRIMARY KEY CLUSTERED ([un_id] ASC)
);
/*
     [un_id]      自增 不填
    [user_id]       留言的用户
    [article_id]    留言的文章
    [message]    留言内容
   
    

*/

CREATE TABLE [dbo].[person_user] (
    [user_id] INT           NOT NULL,
    [address] VARCHAR (100) NULL,
    [phone]   VARCHAR (20)  NULL,
    [mail]    VARCHAR (20)  NULL,
    [job]     VARCHAR (20)  NULL,
    PRIMARY KEY CLUSTERED ([user_id] ASC)
);


CREATE TABLE [dbo].[thumbsup] (
    [un_id]      INT IDENTITY (1, 1) NOT NULL,
    [user_id]    INT NULL,
    [article_id] INT NULL,
    PRIMARY KEY CLUSTERED ([un_id] ASC)
);
/*
    [un_id]           自增 不填
    [user_id]        点赞的用户
    [article_id]     点赞的文章
    

*/

CREATE TABLE [dbo].[user] (
    [user_id]   INT          IDENTITY (1, 1) NOT NULL,
    [username]  VARCHAR (20) NULL,
    [password]  VARCHAR (20) NULL,
    [name]      VARCHAR (20) NULL,
    [attention] INT          NULL,
    [gift]       INT	NULL,
    CONSTRAINT [PK_user] PRIMARY KEY CLUSTERED ([user_id] ASC)
);
/*
    [user_id]  自增  不填
    [username]  用户名
    [password]  密码
    [name]      昵称
    [attention]     关注

*/


CREATE TABLE [dbo].[new] (
    [un_id]   INT IDENTITY (1, 1) NOT NULL,
    [user_id] INT NULL,
    [already] INT NULL,
    [news]    VARCHAR (20)    NULL,
    CONSTRAINT [PK_new] PRIMARY KEY CLUSTERED ([un_id] ASC)
);

/*
    [un_id]   
    [user_id]     被提示的用户
    [already]    是否已读  0 or 1
    [news]    消息通知内容
*/



[user]表的触发器
create trigger tr_after_OrderList on [user] after insert
as
begin
declare @a char(1000),@b char(1000),@c char(1000),@d int
select @a=inserted.username from inserted
select @b=inserted.password from inserted 
select @c=inserted.name from inserted 
select @d=inserted.attention from inserted
declare @e int
select @e=[user].user_id  from [user] where username = @a
insert person_user(user_id) values(@e) 
update person_user
set address='未填写',phone='未填写',mail='未填写',job='未填写'
where person_user.user_id=@e
end


thumbsup 表的触发器
create trigger tr_after_thumbsup on thumbsup after insert
as
begin
declare @a int,@b int
/*insert into thumbsup(user_id,article_id)*/
select @a=inserted.user_id from inserted
select @b=inserted.article_id from inserted 
declare @c int,@d char(10)
select @c=article.user_id  from article where article_id = @a
select @d=[user].name from [user] where [user].user_id=@c
insert new(user_id,already,news) values(@c,0,@d+'给你点赞了') 
end


collect表的触发器
create trigger tr_after_collect on collect after insert
as
begin
declare @a int,@b int
/*insert into collect(user_id,article_id)*/
select @a=inserted.user_id from inserted
select @b=inserted.article_id from inserted 
declare @c int,@d char(10),@e char(20)
select @c=article.user_id  from article where article_id = @b
select @d=[user].name from [user] where [user].user_id=@a
select @e=article.article_title from article where article.article_id=@b
insert new(user_id,already,news) values(@c,0,@d+'收藏了你的文章:'+@e) 
end

attention表的触发器
create trigger tr_after_attention on attention after insert
as
begin
declare @a int,@b int
/*insert into attention(p_user_id,a_user_id)*/
select @a=inserted.p_user_id from inserted
select @b=inserted.a_user_id from inserted 
declare @c char(10)
select @c=[user].name from [user] where [user].user_id=@b
insert new(user_id,already,news) values(@a,0,@c+'关注了你') 
end

message表的触发器
create trigger tr_after_message on message after insert
as
begin
declare @a int,@b int,@c char(100)
/*insert into message(user_id,article_id,message)*/
select @a=inserted.user_id from inserted
select @b=inserted.article_id from inserted 
select @c=inserted.message from inserted
declare @d int,@e char(10)
select @d=article.user_id  from article where article_id = @b
select @e=[user].name from [user] where [user].user_id=@a
insert new(user_id,already,news) values(@d,0,@e+'给你留言了') 
end