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