表名:USER
ID Name State CrateTime
1 qxm 离线 2009-09-14 10:46:33
2 ljh 在线 2009-09-14 10:48:34
3 wfd 在线 2009-09-14 10:49:35表名:MESSAGE ID Name SendTime
1 qxm 2009-09-14 10:46:33要求:
数据库:SQL Server 2005(1)编写一触发器时时检测USER表,当用户在线状态“State”由“在线”改变“离线”,
在MESSAGE表时插入一条记录,MESSAGE.Name=USER.Name and MESSAGE.SendTime=USER.CrateTime 这样的条件。谢谢!
ID Name State CrateTime
1 qxm 离线 2009-09-14 10:46:33
2 ljh 在线 2009-09-14 10:48:34
3 wfd 在线 2009-09-14 10:49:35表名:MESSAGE ID Name SendTime
1 qxm 2009-09-14 10:46:33要求:
数据库:SQL Server 2005(1)编写一触发器时时检测USER表,当用户在线状态“State”由“在线”改变“离线”,
在MESSAGE表时插入一条记录,MESSAGE.Name=USER.Name and MESSAGE.SendTime=USER.CrateTime 这样的条件。谢谢!
FOR UPDATE
AS
BEGIN
IF UPDATE(STATE)
IF EXISTS(SELECT 1 FROM DELETED d INNER JOIN INSERTED i ON d.id=i.id AND d.State='在线' AND i.State='离线')
INSERT [Message] SELECT id,NAME,CreateTime FROM DELETED D
END
for update
as
insert MESSAGE(name,SendTime) select name,CrateTime from
from [USER] a ,inserted b,deleted c
where a.Name=b.Name and b.State='离线' and c.State='在线'
and b.name=c.name
on [USER]
for update
as
begin
insert MESSAGE(ID,Name,SendTime)
select d.id,d.name,d.CrateTime
from delted d,inserted i
where i.id=d.id and i.name=d.name and i.State='离线' and d.State='在线'
endgo
create trigger tri_update on [USER]
for update
as
insert MESSAGE(name,SendTime) select name,CrateTime
from [USER] a ,inserted b,deleted c
where a.Name=b.Name and b.State='离线' and c.State='在线'
and b.name=c.name
IF OBJECT_ID('MESSAGE') IS NOT NULL DROP TABLE [MESSAGE]
IF OBJECT_ID('TIG_USER') IS NOT NULL DROP TRIGGER TIG_USER
GO
CREATE TABLE [USER](
ID INT,
[NAME] VARCHAR(50),
STATE VARCHAR(10),
CREATETIME DATETIME
)
INSERT INTO [USER]
SELECT 1 ,'qxm','离线','2009-09-14 10:46:33' UNION ALL
SELECT 2 ,'ljh','在线','2009-09-14 10:48:34' UNION ALL
SELECT 3 ,'wfd','在线','2009-09-14 10:49:35'
CREATE TABLE [MESSAGE] (
ID INT,
[NAME] VARCHAR(50),
SENDTIME DATETIME
)
GO
CREATE TRIGGER TIG_USER
ON [USER]
AFTER UPDATE
AS
BEGIN
IF EXISTS(
SELECT 1 FROM INSERTED INS
INNER JOIN DELETED DEL ON INS.ID=DEL.ID AND INS.STATE<>DEL.STATE
)
INSERT INTO [MESSAGE]
SELECT INS.ID,INS.[NAME],GETDATE() FROM INSERTED INS
INNER JOIN DELETED DEL ON INS.ID=DEL.ID AND INS.STATE<>DEL.STATE
END
GO
UPDATE [USER] SET STATE='离线'
SELECT * FROM [MESSAGE]
/*
3 wfd 2009-09-14 11:07:13.980
2 ljh 2009-09-14 11:07:13.980
*/
on [USER]
for update
as
begin
insert MESSAGE(Name,SendTime)
select d.name,d.CrateTime
from delted d,inserted i
where i.name=d.name and i.State='离线' and d.State='在线'
endgo
CREATE TRIGGER trTest ON [USER]
FOR UPDATE
AS
BEGIN
IF UPDATE(STATE)
INSERT [Message] SELECT D.id,D.NAME,D.CreateTime FROM DELETED D INNER JOIN INSERTED I
ON D.ID=I.ID
WHERE D.STATE='在线' AND I.STATA='离线'
END
IF OBJECT_ID('USER') IS NOT NULL DROP TABLE [USER]
IF OBJECT_ID('MESSAGE') IS NOT NULL DROP TABLE [MESSAGE]
IF OBJECT_ID('TIG_USER') IS NOT NULL DROP TRIGGER TIG_USER
GO
CREATE TABLE [USER](
ID INT,
[NAME] VARCHAR(50),
STATE VARCHAR(10),
CREATETIME DATETIME
)
INSERT INTO [USER]
SELECT 1 ,'qxm','离线','2009-09-14 10:46:33' UNION ALL
SELECT 2 ,'ljh','在线','2009-09-14 10:48:34' UNION ALL
SELECT 3 ,'wfd','在线','2009-09-14 10:49:35'
CREATE TABLE [MESSAGE] (
ID INT,
[NAME] VARCHAR(50),
SENDTIME DATETIME
)
GO
CREATE TRIGGER TIG_USER
ON [USER]
AFTER UPDATE
AS
BEGIN
IF EXISTS(
SELECT 1 FROM INSERTED INS
INNER JOIN DELETED DEL ON INS.ID=DEL.ID AND INS.STATE<>DEL.STATE AND DEL.STATE='在线'
)
INSERT INTO [MESSAGE]
SELECT INS.ID,INS.[NAME],GETDATE() FROM INSERTED INS
INNER JOIN DELETED DEL ON INS.ID=DEL.ID AND INS.STATE<>DEL.STATE AND DEL.STATE='在线'
END
GO
UPDATE [USER] SET STATE='离线'
SELECT * FROM [MESSAGE]
/*
3 wfd 2009-09-14 11:07:13.980
2 ljh 2009-09-14 11:07:13.980
*/
CREATE TRIGGER tg ON [USER]
FOR UPDATE
AS
IF UPDATE(STATE)
INSERT [Message] SELECT D.id,D.NAME,D.CreateTime
FROM INSERTED I
INNER JOIN DELETED D
ON I.ID=D.ID
AND I.STATA='离线' AND D.STATE='在线'
GO