Create the following triggers and stored procedure in the database.
CREATE TRIGGER Trg_insert_row_into_user_profile
ON dbo.aspnet_Users
FOR INSERT
AS
declare @userid uniqueidentifier
select @userid = userid from inserted
insert into user_profile
values( @userid, null,null,null,null)
CREATE PROCEDURE dbo.DeleteCommunity(@commid int)
AS
begin tran
delete from community_users where commid = @commid;
delete from communities where commid = @commid;
commit tran
CREATE PROCEDURE dbo.GetFriends(@userid as varchar(50))
AS
select u.userid,u.username,fullname
from aspnet_users u join user_profile up
on u.userid = up.userid
where u.userid in (
select friendid from friends
where userid = convert(uniqueidentifier,@userid) );
CREATE PROCEDURE dbo.GetMessages(@userid as varchar(50))
AS
select msgid,userid,username, message, senton
from aspnet_users u join scrapbook s
on u.userid = s.senderid
where s.receiverid = @userid
CREATE PROCEDURE dbo.GetUserDetails(@userid varchar(50))
AS
select u.userid, username, fullname, occupation,
gender = case gender
when 'm' then 'Male'
else 'Female'
end,
dob = convert(varchar(10), dob, 3)
from user_profile up join aspnet_users u
on up.userid = u.userid
where u.userid = convert(uniqueidentifier,@userid);
CREATE PROCEDURE dbo.SearchFriends(@name as varchar(30))
AS
select u.userid,username,fullname
from aspnet_users u join user_profile up
on u.userid = up.userid
where username like '%' + @name + '%'
or fullname like '%' + @name + '%';
CREATE PROCEDURE dbo.SendMessage(@fromuserid uniqueidentifier,@touserid uniqueidentifier,
@text varchar(2000) )
AS
insert into scrapbook
values(@fromuserid,@touserid,@text,getdate());
