Dear All
I am trying to create a DELETE Trigger on a table which will delete a
previous record for a given value entered.
[1] I create a table with two columns called ItemName, TimeStamp (when the
record was last inserted/updated).
[2] I Insert a value of 'Table' into ItemName.
[3] I Insert a value of 'Chair' into ItemName
[4] If I add another entry of 'Table' into ItemName and the Trigger will
find the previous entry for 'Table' and delete it.
What I don't understand is how to get the value on the newly added column
and then serach for the 'duplicate' record to delete?
I apologise if I am not clear as I am a newbie at SQL Server.
Thanks for the continued support of the group and especially Louis Davidson
who helped me with a similar post.
Alastair MacFarlaneyou dont need a triger for delete, you need a trigger for insert that
deletes the record. In the trigger, you have a table 'inserted'. Table
'inserted' has new data, or data you're inserting. So you can write
something like:
CREATE TRIGGER trigerName
ON Table
FOR INSERT
AS
BEGIN
delete
from
table t
inner join inserted i on t.itemname = i.itemname
where
t.timestamp < i.timestamp
END
This will delete ALL records from table with the same itemName and lower
timestamp. You also might want to think about updates.
What should happen if someone updates the ItemName column?
MC
"Alastair MacFarlane" <AlastairMacFarlane@.discussions.microsoft.com> wrote
in message news:8306D794-88DB-4C39-942D-BF62E7822170@.microsoft.com...
> Dear All
> I am trying to create a DELETE Trigger on a table which will delete a
> previous record for a given value entered.
> [1] I create a table with two columns called ItemName, TimeStamp (when the
> record was last inserted/updated).
> [2] I Insert a value of 'Table' into ItemName.
> [3] I Insert a value of 'Chair' into ItemName
> [4] If I add another entry of 'Table' into ItemName and the Trigger will
> find the previous entry for 'Table' and delete it.
> What I don't understand is how to get the value on the newly added column
> and then serach for the 'duplicate' record to delete?
> I apologise if I am not clear as I am a newbie at SQL Server.
> Thanks for the continued support of the group and especially Louis
> Davidson
> who helped me with a similar post.
> Alastair MacFarlane|||Hi
I think you need a trigger for INSERT
Try this one (untested)
create table r (col1 int not null primary key,
col2 char(1) not null)
create trigger my_tr on r for insert
as
if exists (select count(*),col2 from
r group by col2 having count(*)>1)
delete r
from r join inserted i on r.col2=i.col2 and r.col1<i.col1
--test
insert into r values (1,'a')
insert into r values (2,'b')
insert into r values (3,'c')
insert into r values (4,'c')
select * from r
"Alastair MacFarlane" <AlastairMacFarlane@.discussions.microsoft.com> wrote
in message news:8306D794-88DB-4C39-942D-BF62E7822170@.microsoft.com...
> Dear All
> I am trying to create a DELETE Trigger on a table which will delete a
> previous record for a given value entered.
> [1] I create a table with two columns called ItemName, TimeStamp (when the
> record was last inserted/updated).
> [2] I Insert a value of 'Table' into ItemName.
> [3] I Insert a value of 'Chair' into ItemName
> [4] If I add another entry of 'Table' into ItemName and the Trigger will
> find the previous entry for 'Table' and delete it.
> What I don't understand is how to get the value on the newly added column
> and then serach for the 'duplicate' record to delete?
> I apologise if I am not clear as I am a newbie at SQL Server.
> Thanks for the continued support of the group and especially Louis
> Davidson
> who helped me with a similar post.
> Alastair MacFarlane|||An instead of trigger would do well for this
CREATE TABLE furniture (ItemName varchar(512), [TimeStamp] timestamp)
GO
INSERT INTO furniture (ItemName) VALUES ('TABLE')
INSERT INTO furniture (ItemName) VALUES ('CHAIR')
SELECT ItemName, [TimeStamp] FROM furniture
/*
ItemName TimeStamp
---
TABLE 0x0000000000002025
CHAIR 0x0000000000002026
*/
DROP TRIGGER trigger1
GO
CREATE TRIGGER trigger1 ON furniture
INSTEAD OF INSERT
AS
SET NOCOUNT ON
UPDATE furniture SET ItemName = i.ItemName
FROM inserted i INNER JOIN furniture f ON f.ItemName = i.ItemName
INSERT INTO furniture (ItemName) SELECT ItemName FROM inserted i
WHERE NOT EXISTS (SELECT 1 FROM furniture f WHERE i.ItemName = f.ItemName)
SET NOCOUNT OFF
INSERT INTO furniture (ItemName) VALUES ('Ottoman')
INSERT INTO furniture (ItemName) VALUES ('TABLE')
SELECT ItemName, [TimeStamp] FROM furniture
/*
ItemName TimeStamp
-- --
TABLE 0x0000000000002029
CHAIR 0x0000000000002026
Ottoman 0x0000000000002028
*/
--
"Alastair MacFarlane" wrote:
> Dear All
> I am trying to create a DELETE Trigger on a table which will delete a
> previous record for a given value entered.
> [1] I create a table with two columns called ItemName, TimeStamp (when the
> record was last inserted/updated).
> [2] I Insert a value of 'Table' into ItemName.
> [3] I Insert a value of 'Chair' into ItemName
> [4] If I add another entry of 'Table' into ItemName and the Trigger will
> find the previous entry for 'Table' and delete it.
> What I don't understand is how to get the value on the newly added column
> and then serach for the 'duplicate' record to delete?
> I apologise if I am not clear as I am a newbie at SQL Server.
> Thanks for the continued support of the group and especially Louis Davidso
n
> who helped me with a similar post.
> Alastair MacFarlane|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.programming:583593
Alastair,
I'm not sure you really want to create a duplicate and delete it. Here
is another way (Uri, thanks for setting up the data):
create table r (col1 int not null primary key,
col2 char(1) not null)
go
create view r_v
as
select * from r
go
create trigger my_tr on r_v
instead of insert
as
begin
update r set r.col2 = inserted.col2
from r,inserted
where r.col1=inserted.col1
insert into r
select inserted.col1, inserted.col2
from inserted left outer join r
on r.col1=inserted.col1
where r.col1 is null
end
go
--test
insert into r values (1,'a')
insert into r values (2,'b')
insert into r values (3,'c')
-- this will update the row with col1=1
insert into r_v values (1,'c')
go
select * from r
col1 col2
-- --
1 c
2 b
3 c
go
insert into r_v
-- this row is inserted
select 4, 'D'
union all
-- this row is updated
select 2, 'e'
go
select * from r
col1 col2
-- --
1 c
2 e
3 c
4 D
(4 row(s) affected)
go
drop table r
drop view r_v|||Sorry, I thought from your DDL that you were using a column with a datatype
of timestamp. Here is the trigger re-written for a column of datatype
datetime.
DROP TABLE furniture
GO
CREATE TABLE furniture (ItemName char(20), [TimeStamp] datetime)
GO
INSERT INTO furniture VALUES ('TABLE',GETDATE())
INSERT INTO furniture VALUES ('CHAIR',GETDATE())
SELECT ItemName, [TimeStamp] FROM furniture
/*
ItemName TimeStamp
-- --
TABLE 2006-02-09 08:38:35.173
CHAIR 2006-02-09 08:38:35.190
*/
DROP TRIGGER trigger1
GO
CREATE TRIGGER trigger1 ON furniture
INSTEAD OF INSERT
AS
SET NOCOUNT ON
UPDATE furniture SET [TimeStamp] = GETDATE()
FROM inserted i INNER JOIN furniture f ON f.ItemName = i.ItemName
INSERT INTO furniture SELECT ItemName, GETDATE() FROM inserted i
WHERE NOT EXISTS (SELECT 1 FROM furniture f WHERE i.ItemName = f.ItemName)
SET NOCOUNT OFF
INSERT INTO furniture (ItemName) VALUES ('Ottoman')
INSERT INTO furniture (ItemName) VALUES ('TABLE')
SELECT ItemName, [TimeStamp] FROM furniture
/*
ItemName TimeStamp
-- --
TABLE 2006-02-09 08:39:45.410
CHAIR 2006-02-09 08:38:35.190
Ottoman 2006-02-09 08:39:43.440
*/
"Mark Williams" wrote:
> An instead of trigger would do well for this
> CREATE TABLE furniture (ItemName varchar(512), [TimeStamp] timestamp)
> GO
> INSERT INTO furniture (ItemName) VALUES ('TABLE')
> INSERT INTO furniture (ItemName) VALUES ('CHAIR')
> SELECT ItemName, [TimeStamp] FROM furniture
> /*
> ItemName TimeStamp
> ---
> TABLE 0x0000000000002025
> CHAIR 0x0000000000002026
> */
> DROP TRIGGER trigger1
> GO
> CREATE TRIGGER trigger1 ON furniture
> INSTEAD OF INSERT
> AS
> SET NOCOUNT ON
> UPDATE furniture SET ItemName = i.ItemName
> FROM inserted i INNER JOIN furniture f ON f.ItemName = i.ItemName
> INSERT INTO furniture (ItemName) SELECT ItemName FROM inserted i
> WHERE NOT EXISTS (SELECT 1 FROM furniture f WHERE i.ItemName = f.ItemNam
e)
> SET NOCOUNT OFF
> INSERT INTO furniture (ItemName) VALUES ('Ottoman')
> INSERT INTO furniture (ItemName) VALUES ('TABLE')
> SELECT ItemName, [TimeStamp] FROM furniture
> /*
> ItemName TimeStamp
> -- --
> TABLE 0x0000000000002029
> CHAIR 0x0000000000002026
> Ottoman 0x0000000000002028
> */
> --
> "Alastair MacFarlane" wrote:
>|||Mark and others,
Sorry for the delay and thanks for the feedback. I have tested out the
examples provided and they work the way I would expect.
Your help is appreciated.
Alastair MacFarlane
"Mark Williams" wrote:
> Sorry, I thought from your DDL that you were using a column with a datatyp
e
> of timestamp. Here is the trigger re-written for a column of datatype
> datetime.
> DROP TABLE furniture
> GO
> CREATE TABLE furniture (ItemName char(20), [TimeStamp] datetime)
> GO
> INSERT INTO furniture VALUES ('TABLE',GETDATE())
> INSERT INTO furniture VALUES ('CHAIR',GETDATE())
> SELECT ItemName, [TimeStamp] FROM furniture
> /*
> ItemName TimeStamp
> -- --
> TABLE 2006-02-09 08:38:35.173
> CHAIR 2006-02-09 08:38:35.190
> */
> DROP TRIGGER trigger1
> GO
> CREATE TRIGGER trigger1 ON furniture
> INSTEAD OF INSERT
> AS
> SET NOCOUNT ON
> UPDATE furniture SET [TimeStamp] = GETDATE()
> FROM inserted i INNER JOIN furniture f ON f.ItemName = i.ItemName
> INSERT INTO furniture SELECT ItemName, GETDATE() FROM inserted i
> WHERE NOT EXISTS (SELECT 1 FROM furniture f WHERE i.ItemName = f.ItemNam
e)
> SET NOCOUNT OFF
> INSERT INTO furniture (ItemName) VALUES ('Ottoman')
> INSERT INTO furniture (ItemName) VALUES ('TABLE')
> SELECT ItemName, [TimeStamp] FROM furniture
> /*
> ItemName TimeStamp
> -- --
> TABLE 2006-02-09 08:39:45.410
> CHAIR 2006-02-09 08:38:35.190
> Ottoman 2006-02-09 08:39:43.440
> */
> --
> "Mark Williams" wrote:
>
