• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 212
  • Last Modified:

Passing a String to delete a record from VB.NET application

When the User selects to delete the TNUMBER from table WEB_USER , the whole record from table WEB_USER should be deleted.Also, basing upon the WP_USER_ID from table WEB_USER , the row associated with WP_USER_ID in table PROJECT_USER .Could you please help me build the query as I am new to database.This is what my friend sugested as he is a Oracle DBA,can you help me pass this value as a string with vb.NET syntax?

create or replace trigger del_web_user before delete
   on web_user for each row
   begin
      delete from project_user
         where wp_user_id = :old.wp_user_id;
   end;
 
If it sounds little confusing , please let me know so that I can help you understand in a better way.


Table : 1

      WEB_USER

WP_USER_ID   TNUMBER    USER_TYPE

   5                   T111        2
   2                   T123        1
   4                   T156        0
   8                   T155        3
   3                   T134        2

Table : 2

       PROJECT_USER

PROJECT_ID     WP_USER_ID

   1                       5
   2                       4
   1                       2
   3                       8
   1                       3
   3                       5      
0
g46905
Asked:
g46905
  • 2
1 Solution
 
flavoCommented:
WhaT database are you using?
0
 
g46905Author Commented:
Oracle . Please let me know if you have any questions.

Thanks,
0
 
gdexterCommented:
This should be done at the database level with column constraints

You need to have a Foreign Key Constraint in PROJECT_USER with CASCADING DELETE on.

ALTER TABLE "PROJECT_USER"
    ADD (CONSTRAINT "FK_WEB_USER_PROJECT_USER" FOREIGN
    KEY("WP_USER_ID")
    REFERENCES "WEB_USER"("WP_USER_ID")
    ON DELETE CASCADE
0
 
gdexterCommented:
Sorry forgot the closing paren

ALTER TABLE "PROJECT_USER"
    ADD (CONSTRAINT "FK_WEB_USER_PROJECT_USER" FOREIGN
    KEY("WP_USER_ID")
    REFERENCES "WEB_USER"("WP_USER_ID")
    ON DELETE CASCADE)
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now