Delete trigger - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Delete trigger

Author

Topic

BusteR
Starting Member

17 Posts

Posted - 2005-07-07 : 09:18:57

Hi

I want to create a trigger, which will work before deleting a row in one of my tables, i will name it as A.
Before deleting the row in table A, i want the trigger, to delete all rows from table B, which is in relation with A, all the rows which are related with row i want to delete from table A ;]
I now i might not be understood properly but, when i do:
delete from A where A.id = 10

i want the trigger to do

delete from B where B.id_a = 10

I would like You to tell me, how to create the trigger, so it would know what to delete ;] I mean what should i put instead of the "10"?

Best regards and sorry for messing up this post ;]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-07 : 09:30:24

Why do you want to delete B before A?
Is there a referential integrity between these two tables?
Post the table structures

Madhivanan

Failing to plan is Planning to fail

BusteR
Starting Member

17 Posts

Posted - 2005-07-07 : 09:39:33

Yes there is a referential integrity and because of it i can't simply delete a row from within my application.
Table A:
CREATE TABLE [dbo].[maszyny](
[masz_id] [int] IDENTITY(1,1) NOT NULL,
[masz_numer] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[masz_nazwa] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_maszyny] PRIMARY KEY CLUSTERED
(
[masz_id] ASC
) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF

TABLE B:

CREATE TABLE [dbo].[mo](
[mo_id] [int] IDENTITY(1,1) NOT NULL,
[mo_mid] [int] NOT NULL,
[mo_oid] [int] NOT NULL,
CONSTRAINT [PK_mo] PRIMARY KEY CLUSTERED
(
[mo_id] ASC
) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER TABLE [dbo].[mo] WITH CHECK ADD CONSTRAINT [FK_mo_maszyny] FOREIGN KEY( [mo_mid])
REFERENCES [dbo].[maszyny] ( [masz_id])
GO
ALTER TABLE [dbo].[mo] WITH CHECK ADD CONSTRAINT [FK_mo_operacje] FOREIGN KEY( [mo_oid])
REFERENCES [dbo].[operacje] ( [oper_id])
GO

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-07 : 09:45:53

Look for ON DELETE CASCADE in Books On Line

Madhivanan

Failing to plan is Planning to fail

BusteR
Starting Member

17 Posts

Posted - 2005-07-07 : 13:46:49

quote:
Originally posted by madhivanan

Look for ON DELETE CASCADE in Books On Line

Madhivanan

Failing to plan is Planning to fail

Hi again

I've searched the books online, now i knonw that with on delete cascade i can do all i need, but could You please tell me how to alter existing table with this on delete clause?
I just can't find any word about it.

EDIT:

uh.. i have found the alter table syntax, but still i am doing something wron...please correct me with this one


alter table dbo.mo constraint [FK_mo_maszyny]
foreign key mo_mid 
references dbo.	maszyny(masz_id) on delete cascade
go

LOL
so simple ;]
of course i had to drop the constraint and add it again :D

Thx for Your help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources