dinesh_bali
asked on
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
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
http://msdn2.microsoft.com/en-us/library/ms189799.aspx
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'
> 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'
ASKER
Hi,
Can you help me what is 'CASCADE DELETE'
How I can create. How and when it will work?
Kind Regards
Can you help me what is 'CASCADE DELETE'
How I can create. How and when it will work?
Kind Regards
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.
ASKER
This is Good.
But how can I create this?
But how can I create this?
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?
Now, what if, ASPNET_USERS.USERID is refernrenced in some other tables too?
dinesh_bali,
> But how can I create this?
REfER ALTER TABLE in books online
> But how can I create this?
REfER ALTER TABLE in books online
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.
ASKER
Yes, I have tried, but no success??
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_
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_
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[VB_REGISTRATION] WITH CHECK ADD CONSTRAINT [FK_VB_REGISTRATION_ASPNET
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
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
That's really great.
When I executed this
ALTER TABLE [dbo].[vb_Registration] WITH CHECK ADD CONSTRAINT [FK_vb_Registration_aspnet
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
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
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