Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Create Trigger in SQL 2005

Posted on 2006-11-29
13
Medium Priority
?
2,551 Views
Last Modified: 2011-08-18
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
0
Comment
Question by:dinesh_bali
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
13 Comments
 
LVL 29

Expert Comment

by:QPR
ID: 18042708
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 18042875
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
 

Author Comment

by:dinesh_bali
ID: 18042898
Hi,

Can you help me what is 'CASCADE DELETE'

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

Kind Regards
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 18042931
   
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
 

Author Comment

by:dinesh_bali
ID: 18042951
This is Good.

But how can I create this?
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 18042953
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
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 18042980
dinesh_bali,
> But how can I create this?

REfER   ALTER TABLE  in books online
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 18043060
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
 

Author Comment

by:dinesh_bali
ID: 18043192
Yes, I have tried, but no success??
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 18043232
                                       
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
 

Author Comment

by:dinesh_bali
ID: 18043381
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
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 2000 total points
ID: 18043406
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
 

Author Comment

by:dinesh_bali
ID: 18043788
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

Featured Post

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

604 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question