DELETE Trigger

6 Jan 2006 2:34 PM

Redowl

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.

6 Jan 2006 2:39 PM

Jens

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.

9 Jan 2006 10:32 AM

Redowl

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.

Show quote

"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.
>
>

6 Jan 2006 2:45 PM

Jens

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

6 Jan 2006 2:51 PM

Mark Williams

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.

--

Show quote

"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 certain
> fields match those of the record to be inserted.
>
> Thanks.
>
>
>

9 Jan 2006 10:24 AM

Redowl

Thanks everybody for the responses

Mark, will your suggestion not delete the newly added row as well ?

Show quote

"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 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 certain
> > fields match those of the record to be inserted.
> >
> > Thanks.
> >
> >
> >