DELETE Trigger

Hi,
I am pretty sure this is fairly straightforward, I would like to use a
trigger to delete an existing record from a table where the value of certain
fields match those of the record to be inserted.
Thanks.Hi Redowl,
CREATE TRIGGER SomeDelTrigger ON SomeTable
FOR INSERT
AS
BEGIN
DELETE
FROM SomeOtherTable
INNER JOIN INSERTED
ON SomeOtherTable.COl01 = INSERTED.Col01
-- (...) other columns to join on
END
HTH, Jens Suessmeyer|||Without knowing the details of your underlying tables
CREATE TRIGGER deleteRows ON [YourTable]
AFTER INSERT
AS
DELETE FROM [YourTable] WHERE [YourTable].col1=inserted.col1
AND [YourTable].col2=inserted.col2 --check to match more if necessary
Keep in mind that an AFTER trigger will only execute if the statement that
caused the trigger to fire would have normally succeeded. If you are trying
to delete a row that would have caused a violation of a constraint, use a
INSTEAD OF trigger.
--
"Redowl" wrote:

> Hi,
> I am pretty sure this is fairly straightforward, I would like to use a
> trigger to delete an existing record from a table where the value of certa
in
> fields match those of the record to be inserted.
> Thanks.
>
>|||CREATE TRIGGER SomeDelTrigger ON SomeTable
FOR INSERT
AS
BEGIN
DELETE
FROM SomeotherTable
INNER JOIN INSERTED
ON SomeotherTable.Col1 = INSERTED.Col1
--(...) and so on, name the joined columns
END
HTH, jens Suessmeyer.|||Thanks everybody for the responses
Mark, will your suggestion not delete the newly added row as well ?
"Mark Williams" wrote:
> Without knowing the details of your underlying tables
> CREATE TRIGGER deleteRows ON [YourTable]
> AFTER INSERT
> AS
> DELETE FROM [YourTable] WHERE [YourTable].col1=inserted.col1
> AND [YourTable].col2=inserted.col2 --check to match more if necessary
>
> Keep in mind that an AFTER trigger will only execute if the statement that
> caused the trigger to fire would have normally succeeded. If you are tryin
g
> to delete a row that would have caused a violation of a constraint, use a
> INSTEAD OF trigger.
> --
>
> "Redowl" wrote:
>|||Jens,
Thanks for the response. I want to delete an existing row from the same
table I am inserting into.
I want to delete any existing rows that match a certain criteria. e.g A new
row could have surname = 'Jones' and firstname = 'Bob' and age='30'. I want
to check if there is already a row with surname = 'Jones' and firstname =
'Bob' and delete this?
Grateful for any help.
"Jens" wrote:

> CREATE TRIGGER SomeDelTrigger ON SomeTable
> FOR INSERT
> AS
> BEGIN
> DELETE
> FROM SomeotherTable
> INNER JOIN INSERTED
> ON SomeotherTable.Col1 = INSERTED.Col1
> --(...) and so on, name the joined columns
> END
>
> HTH, jens Suessmeyer.
>