Create Trigger in SQL 2005

Hi,

I am working on SQL 2005

I want to create trigger, which deletes the data from its all depending tables.

Which has foreign key attached

For Example

I have

Table: VB_REGISTRATION
Column: SITEID, USERID

I want that when the data is deleted from table VB_REGISTRATION, then the corresponding data for the user present in the

TABLE: ASPNET_USERS also get deleted.

TABLE: ASPNET_USERS has
Column: USERID

Can anyone help me

Thanks
dinesh_baliAsked:
Who is Participating?
 
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
try this


CREATE TABLE [dbo].[ASPNET_USERS](
      [userid] [int] IDENTITY(1,1) NOT NULL,
      [username] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [PK_ASPNET_USERS] PRIMARY KEY CLUSTERED
(
      [userid] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[VB_REGISTRATION](
      [UserID] [int] NULL,
      [siteID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[VB_REGISTRATION]  WITH CHECK ADD  CONSTRAINT [FK_VB_REGISTRATION_ASPNET_USERS] FOREIGN KEY([UserID])
REFERENCES [dbo].[ASPNET_USERS] ([userid])
ON DELETE CASCADE
GO


CREATE TABLE [dbo].[ASPnet_membership](
      [UserID] [int] NULL,
      [Some] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[ASPnet_membership]  WITH CHECK ADD  CONSTRAINT [FK_ASPnet_membership_ASPNET_USERS] FOREIGN KEY([UserID])
REFERENCES [dbo].[ASPNET_USERS] ([userid])
ON DELETE CASCADE
GO
INSERT INTO dbo.ASPNET_USERS
SELECT 'One'
UNION
SELECT 'Two'
UNION
SELECT 'Three'
UNION
SELECT 'Four'


INSERT INTO dbo.VB_REGISTRATION
SELECT 1, 'SiteOne'
UNION ALL
SELECT 2, 'SiteTwo'
UNION ALL
SELECT 1, 'SiteThree'

INSERT INTO [dbo].[ASPnet_membership]
SELECT 1, 'Maps One'


SELECT * FROM dbo.ASPNET_USERS
SELECT * FROM dbo.VB_REGISTRATION
SELECT * FROM dbo.ASPnet_membership
DELETE FROM dbo.ASPNET_USERS WHERE userid =1
SELECT * FROM dbo.ASPNET_USERS
SELECT * FROM dbo.VB_REGISTRATION
SELECT * FROM dbo.ASPnet_membership


0
 
Aneesh RetnakaranDatabase AdministratorCommented:
dinesh_bali,
> I want to create trigger, which deletes the data from its all depending tables.

as an altenative to trigger in this situation, you can enable  'CASCADE DELETE'
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
dinesh_baliAuthor Commented:
Hi,

Can you help me what is 'CASCADE DELETE'

How I can create. How and when it will work?

Kind Regards
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
   
dinesh_bali,
> Can you help me what is 'CASCADE DELETE'

ON DELETE CASCADE

Specifies that if an attempt is made to delete a row with a key referenced by foreign keys in existing rows in other tables, all rows containing those foreign keys are also deleted. If cascading referential actions have also been defined on the target tables, the specified cascading actions are also taken for the rows deleted from those tables.





0
 
dinesh_baliAuthor Commented:
This is Good.

But how can I create this?
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
In your case , what you are trying to do is , you are trying to delete from a reference table and you need to delete the curresponding records from the master table(ASPNET_USERS).
Now, what if, ASPNET_USERS.USERID is refernrenced in some other tables too?
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
dinesh_bali,
> But how can I create this?

REfER   ALTER TABLE  in books online
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
You just have to enable 'ON DELETE CASCADE' on ASPNET_USERS.USERID , and just try to delete that userId from 'ASPNET_USERS'. I assume that this is the mater table , by doing this it will remove all the foreign key references.
0
 
dinesh_baliAuthor Commented:
Yes, I have tried, but no success??
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
                                       
dinesh_bali,
> Yes, I have tried, but no success??


CREATE TABLE [dbo].[ASPNET_USERS](
    [userid] [int] IDENTITY(1,1) NOT NULL,
    [username] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [PK_ASPNET_USERS] PRIMARY KEY CLUSTERED
(
    [userid] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[VB_REGISTRATION](
    [UserID] [int] NULL,
    [siteID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[VB_REGISTRATION]  WITH CHECK ADD  CONSTRAINT [FK_VB_REGISTRATION_ASPNET_USERS] FOREIGN KEY([UserID])
REFERENCES [dbo].[ASPNET_USERS] ([userid])
ON DELETE CASCADE
GO


INSERT INTO dbo.ASPNET_USERS
SELECT 'One'
UNION
SELECT 'Two'
UNION
SELECT 'Three'
UNION
SELECT 'Four'


INSERT INTO dbo.VB_REGISTRATION
SELECT 1, 'SiteOne'
UNION ALL
SELECT 2, 'SiteTwo'
UNION ALL
SELECT 1, 'SiteThree'


SELECT * FROM dbo.ASPNET_USERS
SELECT * FROM dbo.VB_REGISTRATION
DELETE FROM dbo.ASPNET_USERS WHERE userid =1
SELECT * FROM dbo.ASPNET_USERS
SELECT * FROM dbo.VB_REGISTRATION


0
 
dinesh_baliAuthor Commented:
Thanks allot.

Can I add one more table to it

Table Name: ASPnet_membership
Column: UserId

Which is linked to tble ASPNET_USERS

Many Thanks Once again for what u have done for me
0
 
dinesh_baliAuthor Commented:
Hi,

That's really great.

When I executed this

ALTER TABLE [dbo].[vb_Registration]  WITH CHECK ADD  CONSTRAINT [FK_vb_Registration_aspnet_Users] FOREIGN KEY([Userid])
REFERENCES [dbo].[aspnet_Users] ([UserId]) ON DELETE CASCADE

I get the error

Msg 2714, Level 16, State 4, Line 1
There is already an object named 'FK_vb_Registration_aspnet_Users' in the database.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.

Previously this script was of with Cascade, I mean the script was

ALTER TABLE [dbo].[vb_Registration]  WITH CHECK ADD  CONSTRAINT [FK_vb_Registration_aspnet_Users] FOREIGN KEY([Userid])
REFERENCES [dbo].[aspnet_Users] ([UserId])

How can I modify it.

But below is executed perfect

ALTER TABLE [dbo].[aspnet_Membership]  WITH CHECK ADD FOREIGN KEY([UserId])
REFERENCES [dbo].[aspnet_Users] ([UserId])
ON DELETE CASCADE

Many Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.