Trigger to delete record from one table with check on another table.

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Kenneth Osenbroch
    #1

    Trigger to delete record from one table with check on another table.

    Hi. I want to create a trigger that only allows delete from table A if
    corresponding record in table B does not exist. Any idea on how this
    can be done? Thanks, Kenneth.
    Tags: None
    • Laconic2
      #2
      Re: Trigger to delete record from one table with check on another table.

      Is there a column in table B that references the primary key of table A?

      If so, won't the constraint prevent the deletes you want to prevent?


        Comment

        • Romeo Olympia
          #3
          Re: Trigger to delete record from one table with check on another table.

          Don't you wanna use referential integrity constraints to achieve this?

          kenneth.osenbro ch@telenor.com (Kenneth Osenbroch) wrote in message news:<ce7df6a2. 0405260105.55c4 489@posting.goo gle.com>...
          Hi. I want to create a trigger that only allows delete from table A if
          corresponding record in table B does not exist. Any idea on how this
          can be done? Thanks, Kenneth.

            Comment

            • Ed prochak
              #4
              Re: Trigger to delete record from one table with check on another table.

              kenneth.osenbro ch@telenor.com (Kenneth Osenbroch) wrote in message news:<ce7df6a2. 0405260105.55c4 489@posting.goo gle.com>...
              Hi. I want to create a trigger that only allows delete from table A if
              corresponding record in table B does not exist. Any idea on how this
              can be done? Thanks, Kenneth.
              Sounds more like you want a reference constraint.
              Do you have Primary keys and Foreign Keys set up on the corredsponding
              tables? Looks like from the description table A is the parent table
              (wiht a primary key) and table B is the child table (with a foreign
              key that references the primary key of table A)

              HTH,
              ed

                Comment

                • Kenneth Osenbroch
                  #5
                  Re: Trigger to delete record from one table with check on another table.

                  ed.prochak@magi cinterface.com (Ed prochak) wrote in message news:<4b5394b2. 0405260843.2029 300c@posting.go ogle.com>...
                  kenneth.osenbro ch@telenor.com (Kenneth Osenbroch) wrote in message news:<ce7df6a2. 0405260105.55c4 489@posting.goo gle.com>...
                  Hi. I want to create a trigger that only allows delete from table A if
                  corresponding record in table B does not exist. Any idea on how this
                  can be done? Thanks, Kenneth.
                  >
                  Sounds more like you want a reference constraint.
                  Do you have Primary keys and Foreign Keys set up on the corredsponding
                  tables? Looks like from the description table A is the parent table
                  (wiht a primary key) and table B is the child table (with a foreign
                  key that references the primary key of table A)
                  >
                  HTH,
                  ed
                  Hi, and thanks for your reply.

                  The thing is that it is not possible to use constraints based on PK's
                  and FK's in this database (the relations are placed on the applicaton
                  layer - hence it doesn't exist any PK's or FK's).

                  So: I figure trigger is the only solution to ensure the above
                  scenario. Any ideas on how the trigger would look?

                  Thanks,
                  Kenneth

                    Comment

                    • Ed prochak
                      #6
                      Re: Trigger to delete record from one table with check on another table.

                      kenneth.osenbro ch@telenor.com (Kenneth Osenbroch) wrote in message news:<ce7df6a2. 0406030112.2b7f 3e5b@posting.go ogle.com>...
                      ed.prochak@magi cinterface.com (Ed prochak) wrote in message news:<4b5394b2. 0405260843.2029 300c@posting.go ogle.com>...
                      kenneth.osenbro ch@telenor.com (Kenneth Osenbroch) wrote in message news:<ce7df6a2. 0405260105.55c4 489@posting.goo gle.com>...
                      Hi. I want to create a trigger that only allows delete from table A if
                      corresponding record in table B does not exist. Any idea on how this
                      can be done? Thanks, Kenneth.
                      Sounds more like you want a reference constraint.
                      Do you have Primary keys and Foreign Keys set up on the corredsponding
                      tables? Looks like from the description table A is the parent table
                      (wiht a primary key) and table B is the child table (with a foreign
                      key that references the primary key of table A)

                      HTH,
                      ed
                      >
                      Hi, and thanks for your reply.
                      >
                      The thing is that it is not possible to use constraints based on PK's
                      and FK's in this database (the relations are placed on the applicaton
                      layer - hence it doesn't exist any PK's or FK's).
                      >
                      So: I figure trigger is the only solution to ensure the above
                      scenario. Any ideas on how the trigger would look?
                      >
                      Thanks,
                      Kenneth
                      I cannot think of any way for a trigger to STOP a delete, other than
                      maybe throwing an exception?

                      What do you mean the relations are in the application layer?
                      (CAUTION: That's the path to hell!)

                      Do you have two tables in the database or not?

                      It looks like you are using a screwdriver as a hammer. It doesn't work
                      well and in the end both the nail and the screwdriver are in bad
                      shape.

                      Sorry I cannot be more help, but I have a genetic disorder that
                      debilitates my programming skills when faced with an absurd
                      assignment. 8^)

                      Good luck! you are going to need it.
                      Ed

                        Comment

                        • Kenneth Osenbroch
                          #7
                          Re: Trigger to delete record from one table with check on another table.

                          ed.prochak@magi cinterface.com (Ed prochak) wrote in message news:<4b5394b2. 0406031025.3f01 745e@posting.go ogle.com>...
                          kenneth.osenbro ch@telenor.com (Kenneth Osenbroch) wrote in message news:<ce7df6a2. 0406030112.2b7f 3e5b@posting.go ogle.com>...
                          ed.prochak@magi cinterface.com (Ed prochak) wrote in message news:<4b5394b2. 0405260843.2029 300c@posting.go ogle.com>...
                          kenneth.osenbro ch@telenor.com (Kenneth Osenbroch) wrote in message news:<ce7df6a2. 0405260105.55c4 489@posting.goo gle.com>...
                          Hi. I want to create a trigger that only allows delete from table A if
                          corresponding record in table B does not exist. Any idea on how this
                          can be done? Thanks, Kenneth.
                          >
                          Sounds more like you want a reference constraint.
                          Do you have Primary keys and Foreign Keys set up on the corredsponding
                          tables? Looks like from the description table A is the parent table
                          (wiht a primary key) and table B is the child table (with a foreign
                          key that references the primary key of table A)
                          >
                          HTH,
                          ed
                          Hi, and thanks for your reply.

                          The thing is that it is not possible to use constraints based on PK's
                          and FK's in this database (the relations are placed on the applicaton
                          layer - hence it doesn't exist any PK's or FK's).

                          So: I figure trigger is the only solution to ensure the above
                          scenario. Any ideas on how the trigger would look?

                          Thanks,
                          Kenneth
                          >
                          I cannot think of any way for a trigger to STOP a delete, other than
                          maybe throwing an exception?
                          >
                          What do you mean the relations are in the application layer?
                          (CAUTION: That's the path to hell!)
                          >
                          Do you have two tables in the database or not?
                          >
                          It looks like you are using a screwdriver as a hammer. It doesn't work
                          well and in the end both the nail and the screwdriver are in bad
                          shape.
                          >
                          Sorry I cannot be more help, but I have a genetic disorder that
                          debilitates my programming skills when faced with an absurd
                          assignment. 8^)
                          >
                          Good luck! you are going to need it.
                          Ed
                          :o)

                          I know! Consider the case closed.

                          Cheers,
                          Kenneth

                            Comment

                            Previous template Next
                            Working...