更改列的SQL触发器？ [英] SQL Trigger for changed columns?
问题描述
我之前从未写过触发器，现在正在看光。是
有一种方法来编写一个触发器，这样如果用户在一个表上更改了
a单行中的任何列，那么触发器将写入值
这（这些）行到第二个表。我不想要未更改的
列，只需更改列（带列名...）
谢谢，
lq < 2004年4月12日18:36:24 -0700，Lauren Quantrell写道：
我有从来没有写过触发器，现在看到了光。有没有办法编写一个触发器，这样如果用户在一个表上的一行中更改了任何一列，那么触发器会将这些（这些）行的值写入a第二张桌子。我不想要未更改的列，只需更改的列（带列名...）
谢谢，
lq
这样的东西，也许吧？
CREATE TABLE Lauren
（KeyCol int not null主键，
DataCol1 int not null，
DataCol2 int null，
DataCol3 int not null）
go
CREATE TABLE Quantrell
（KeyCol int not null主键，
ColName char（8）not null，
NewData int not null）
go
CREATE TRIGGER LaurenQuantrell
on Lauren更新后
AS
如果更新（KeyCol） ）
BEGIN
RAISERROR（''不要改变关键栏!!''，16,1）
ROLLBACK TRANSACTION
返回
结束
INSERT Quantrell（KeyCol，ColName，NewData）
SELECT inserted.KeyCol ，''DataCol1''，inserted.DataCol1
F. ROM插入
INNER JOIN删除ON deleted.KeyCol = inserted.KeyCol
WHERE inserted.DataCol1<> deleted.DataCol1
INSERT Quantrell（KeyCol，ColName，NewData）
SELECT inserted.KeyCol，''DataCol2''，inserted.DataCol1
FROM插入
INNER JOIN删除ON deleted.KeyCol = inserted.KeyCol
WHERE inserted.DataCol2<> deleted.DataCol2
OR（inserted.DataCol2 IS NULL AND deleted.DataCol2 IS NOT NULL）
OR（inserted.DataCol2 IS NOT NULL AND deleted.DataCol2 IS NULL）
INSERT Quantrell（KeyCol，ColName，NewData）
SELECT inserted.KeyCol，''DataCol3''，inserted.DataCol3
FROM插入
INNER JOIN删除ON deleted.KeyCol = inserted.KeyCol
WHERE inserted.DataCol3<> deleted.DataCol3
go
Best，Hugo
-
（删除_NO_和_SPAM_获取我的电子邮件地址）
2004年4月12日18:36:24 -0700，Lauren Quantrell写道：
我之前从未写过触发器，现在正在看光。有没有办法编写一个触发器，这样如果用户在一个表上的一行中更改了任何一列，那么触发器会将这些（这些）行的值写入a第二张桌子。我不想要未更改的列，只需更改的列（带列名...）
谢谢，
lq
这样的东西，也许吧？
CREATE TABLE Lauren
（KeyCol int not null主键，
DataCol1 int not null，
DataCol2 int null，
DataCol3 int not null）
go
CREATE TABLE Quantrell
（KeyCol int not null主键，
ColName char（8）not null，
NewData int not null）
go
CREATE TRIGGER LaurenQuantrell
on Lauren更新后
AS
如果更新（KeyCol） ）
BEGIN
RAISERROR（''不要改变关键栏!!''，16,1）
ROLLBACK TRANSACTION
返回
结束
INSERT Quantrell（KeyCol，ColName，NewData）
SELECT inserted.KeyCol ，''DataCol1''，inserted.DataCol1
F. ROM插入
INNER JOIN删除ON deleted.KeyCol = inserted.KeyCol
WHERE inserted.DataCol1<> deleted.DataCol1
INSERT Quantrell（KeyCol，ColName，NewData）
SELECT inserted.KeyCol，''DataCol2''，inserted.DataCol1
FROM插入
INNER JOIN删除ON deleted.KeyCol = inserted.KeyCol
WHERE inserted.DataCol2<> deleted.DataCol2
OR（inserted.DataCol2 IS NULL AND deleted.DataCol2 IS NOT NULL）
OR（inserted.DataCol2 IS NOT NULL AND deleted.DataCol2 IS NULL）
INSERT Quantrell（KeyCol，ColName，NewData）
SELECT inserted.KeyCol，''DataCol3''，inserted.DataCol3
FROM插入
INNER JOIN删除ON deleted.KeyCol = inserted.KeyCol
WHERE inserted.DataCol3<> deleted.DataCol3
go
Best，Hugo
-
（删除_NO_和_SPAM_获取我的电子邮件地址）
Hugo，
感谢您的代码！
现在，擦是所有列都是整数类型。我有
ntext，nvarchar，int，smallint，tinyint ...
lq
Hugo Kornelis< hugo@pe_NO_rFact.in_SPAM_fo>在消息新闻中写道：< e6 ******************************** @ 4ax.com>。 ..2004年4月12日18:36:24 -0700，Lauren Quantrell写道：
我以前从未写过触发器，现在看到了光。有没有办法编写一个触发器，这样如果用户在一个表上的一行中更改了任何一列，那么触发器会将这些（这些）行的值写入a第二张桌子。我不想要未更改的列，只需更改的列（带列名......）
谢谢，
lq
像这样，也许？
CREATE TABLE Lauren
（KeyCol int not null主键，
DataCol1 int not null，
DataCol2 int null，
DataCol3 int not null）
go
CREATE TABLE Quantrell
（KeyCol int not null主键，
ColName char（8）not null，
NewData int not null）<创建触发器LaurenQuantrell
ON Lauren更新后
如果更新（KeyCol）
BEGIN
RAISERROR（''不要改变关键栏!!''，16,1）
ROLLBACK交易
返回
INSERT Quantrell（KeyCol，ColName，NewData）
SELECT inserted.KeyCol，''DataCol1''，inserted.DataCol1
FROM inserted
INNER JOIN删除ON删除.KeyCol = inserted.KeyCol
WHERE inserted.DataCol1<>删除.DataCol1
INSERT Quantrell（KeyCol，ColName，NewData）
SELECT inserted.KeyCol，''DataCol2''，inserted.DataCol1
FROM inserted
INNER JOIN删除ON deleted.KeyCol = inserted.KeyCol
WHERE inserted.DataCol2<> deleted.DataCol2
OR（inserted.DataCol2 IS NULL AND deleted.DataCol2 IS NOT NULL）
OR（inserted.DataCol2 IS NOT NULL AND deleted.DataCol2 IS NULL）
INSERT Quantrell（KeyCol，ColName，NewData）
SELECT inserted.KeyCol，''DataCol3''，inserted.DataCol3
FROM inserted
INNER JOIN删除ON deleted.KeyCol = inserted.KeyCol
WHERE inserted.DataCol3<> deleted.DataCol3
go
Best，Hugo
I have never written a trigger before and now am seeing the light. Is
there a way to write a trigger so that if a user changes any column in
a single row on one table then the trigger will write the value of
this (these) rows to a second table. I don''t want the unchanged
columns, just the changed columns (with column names...)
Thanks,
lq
On 12 Apr 2004 18:36:24 -0700, Lauren Quantrell wrote:
I have never written a trigger before and now am seeing the light. Is
there a way to write a trigger so that if a user changes any column in
a single row on one table then the trigger will write the value of
this (these) rows to a second table. I don''t want the unchanged
columns, just the changed columns (with column names...)
Thanks,
lq
Something like this, maybe?
CREATE TABLE Lauren
(KeyCol int not null primary key,
DataCol1 int not null,
DataCol2 int null,
DataCol3 int not null)
go
CREATE TABLE Quantrell
(KeyCol int not null primary key,
ColName char(8) not null,
NewData int not null)
go
CREATE TRIGGER LaurenQuantrell
ON Lauren AFTER UPDATE
AS
IF UPDATE(KeyCol)
BEGIN
RAISERROR (''Don''t change the key column!!'', 16, 1)
ROLLBACK TRANSACTION
RETURN
END
INSERT Quantrell (KeyCol, ColName, NewData)
SELECT inserted.KeyCol, ''DataCol1'', inserted.DataCol1
FROM inserted
INNER JOIN deleted ON deleted.KeyCol = inserted.KeyCol
WHERE inserted.DataCol1 <> deleted.DataCol1
INSERT Quantrell (KeyCol, ColName, NewData)
SELECT inserted.KeyCol, ''DataCol2'', inserted.DataCol1
FROM inserted
INNER JOIN deleted ON deleted.KeyCol = inserted.KeyCol
WHERE inserted.DataCol2 <> deleted.DataCol2
OR (inserted.DataCol2 IS NULL AND deleted.DataCol2 IS NOT NULL)
OR (inserted.DataCol2 IS NOT NULL AND deleted.DataCol2 IS NULL)
INSERT Quantrell (KeyCol, ColName, NewData)
SELECT inserted.KeyCol, ''DataCol3'', inserted.DataCol3
FROM inserted
INNER JOIN deleted ON deleted.KeyCol = inserted.KeyCol
WHERE inserted.DataCol3 <> deleted.DataCol3
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
On 12 Apr 2004 18:36:24 -0700, Lauren Quantrell wrote:
I have never written a trigger before and now am seeing the light. Is
there a way to write a trigger so that if a user changes any column in
a single row on one table then the trigger will write the value of
this (these) rows to a second table. I don''t want the unchanged
columns, just the changed columns (with column names...)
Thanks,
lq
Something like this, maybe?
CREATE TABLE Lauren
(KeyCol int not null primary key,
DataCol1 int not null,
DataCol2 int null,
DataCol3 int not null)
go
CREATE TABLE Quantrell
(KeyCol int not null primary key,
ColName char(8) not null,
NewData int not null)
go
CREATE TRIGGER LaurenQuantrell
ON Lauren AFTER UPDATE
AS
IF UPDATE(KeyCol)
BEGIN
RAISERROR (''Don''t change the key column!!'', 16, 1)
ROLLBACK TRANSACTION
RETURN
END
INSERT Quantrell (KeyCol, ColName, NewData)
SELECT inserted.KeyCol, ''DataCol1'', inserted.DataCol1
FROM inserted
INNER JOIN deleted ON deleted.KeyCol = inserted.KeyCol
WHERE inserted.DataCol1 <> deleted.DataCol1
INSERT Quantrell (KeyCol, ColName, NewData)
SELECT inserted.KeyCol, ''DataCol2'', inserted.DataCol1
FROM inserted
INNER JOIN deleted ON deleted.KeyCol = inserted.KeyCol
WHERE inserted.DataCol2 <> deleted.DataCol2
OR (inserted.DataCol2 IS NULL AND deleted.DataCol2 IS NOT NULL)
OR (inserted.DataCol2 IS NOT NULL AND deleted.DataCol2 IS NULL)
INSERT Quantrell (KeyCol, ColName, NewData)
SELECT inserted.KeyCol, ''DataCol3'', inserted.DataCol3
FROM inserted
INNER JOIN deleted ON deleted.KeyCol = inserted.KeyCol
WHERE inserted.DataCol3 <> deleted.DataCol3
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Hugo,
Thanks for that code!
Now, the rub is that all the columns are integer types. I''ve got
ntext, nvarchar, int, smallint, tinyint...
lq
Hugo Kornelis <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message news:<e6********************************@4ax.com>. ..On 12 Apr 2004 18:36:24 -0700, Lauren Quantrell wrote:I have never written a trigger before and now am seeing the light. Is
there a way to write a trigger so that if a user changes any column in
a single row on one table then the trigger will write the value of
this (these) rows to a second table. I don''t want the unchanged
columns, just the changed columns (with column names...)
Thanks,
lq
Something like this, maybe?
CREATE TABLE Lauren
(KeyCol int not null primary key,
DataCol1 int not null,
DataCol2 int null,
DataCol3 int not null)
go
CREATE TABLE Quantrell
(KeyCol int not null primary key,
ColName char(8) not null,
NewData int not null)
go
CREATE TRIGGER LaurenQuantrell
ON Lauren AFTER UPDATE
AS
IF UPDATE(KeyCol)
BEGIN
RAISERROR (''Don''t change the key column!!'', 16, 1)
ROLLBACK TRANSACTION
RETURN
END
INSERT Quantrell (KeyCol, ColName, NewData)
SELECT inserted.KeyCol, ''DataCol1'', inserted.DataCol1
FROM inserted
INNER JOIN deleted ON deleted.KeyCol = inserted.KeyCol
WHERE inserted.DataCol1 <> deleted.DataCol1
INSERT Quantrell (KeyCol, ColName, NewData)
SELECT inserted.KeyCol, ''DataCol2'', inserted.DataCol1
FROM inserted
INNER JOIN deleted ON deleted.KeyCol = inserted.KeyCol
WHERE inserted.DataCol2 <> deleted.DataCol2
OR (inserted.DataCol2 IS NULL AND deleted.DataCol2 IS NOT NULL)
OR (inserted.DataCol2 IS NOT NULL AND deleted.DataCol2 IS NULL)
INSERT Quantrell (KeyCol, ColName, NewData)
SELECT inserted.KeyCol, ''DataCol3'', inserted.DataCol3
FROM inserted
INNER JOIN deleted ON deleted.KeyCol = inserted.KeyCol
WHERE inserted.DataCol3 <> deleted.DataCol3
go
Best, Hugo
这篇关于更改列的SQL触发器？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
