Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Trigger to be written with 2 Tables

Posted on 2004-09-23
18
Medium Priority
?
302 Views
Last Modified: 2006-11-17
Trigger
==========

I have two tables 1) ToysBox 2)Toys

ToysBox
======
ToyBoxID (PK)
ToyBoxName        NotNULL
ToyBoxOpenDate    NotNull
toyBoxCloseDate   NULL

Toys
===
Toy_ID
ToyBoxID (FK)
Toycolor            NOTNULL
ToyDescription      NOTNULL
ToyAssignmentStatus     'N'       (when an entry is made status is assigned N (which is not assigneD)


Toy_assignment_status "N" is default (ie NOT ASSIGNED)
(when Toy is assigned to a wearhouse  its status changes to A Or when toy is missing in a box its status is changed to 'M')

I want to write a Trigger which fires when all toys are assigned  for a particular BoxID
We need to check in toys table if toy_assignment_status is not "N" for the particular TOYBOXID and
Udpate  todays date in ToyBoxcloseDate Column  in ToysBox table.(this indicates that a ToyBox has been closed)
Pls help me with this.
0
Comment
Question by:sainavya1215
[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
  • 8
  • 7
  • 2
  • +1
18 Comments
 
LVL 10

Expert Comment

by:imrancs
ID: 12131737
please explain your problem with some example data.


Imran
0
 
LVL 10

Expert Comment

by:imrancs
ID: 12131804
create trigger tgr_Toys on Toys
For Update
AS
BEGIN

declare @BoxId int

select @boxid = boxId from inserted

--Check if Status is updated
If Update(ToyAssignmentStatus)
  Being
       --Check if there no Toys with Status 'N'
       If Not Exists(Select * From Toys Where ToyAssignmentStatus='N')
            Update ToysBox Set toyBoxCloseDate = GetDate() Where BoxId = @BoxId
  End

END


Imran
0
 
LVL 10

Expert Comment

by:imrancs
ID: 12131812
The trigger will fire every time you update the Toys table and will performe the following operations
 - Frist it will check if the ToyAssignmentStatus is updated
 - Then it will update the ToysBox.toyBoxCloseDate to current datetime if there is no Toy with ToyAssignmentStatus = 'N'



Imran
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 2

Expert Comment

by:vidnan123
ID: 12131876
I believe that the trigger must be as follows.....

create trigger tgr_Toys on Toys
For Insert, Update
AS
BEGIN

declare @BoxId int,
            @Status char(1)

select @boxid = boxId,
          @Status = ToyAssignmentStatus    
from inserted

IF @Status <> 'N'
BEGIN
            Update ToysBox
            Set  toyBoxCloseDate = GetDate()
            Where BoxId = @BoxId

             IF @@ERROR <> 0
                     RAISERROR ('', 16, 1)
END

END
0
 
LVL 2

Expert Comment

by:vidnan123
ID: 12131889
The above trigger will fire every time a new toy is created or updated.

In both cases, it will check for the toy assignment status. If the status is not 'N', it will update the toyBoxCloseDate in the toyBox table to the current date.

Hope I have understood your requirement correctly.

0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12132491
Personnally , I would ditch the close date in the toybox table and have an allocateddate in the toy table with a default of getdatE()

then you can have a simple query to determine the closure date.   No Triggers required then
0
 

Author Comment

by:sainavya1215
ID: 12138211
Hi imran

firstly I entered 2 Records in toysbox table lets say boxid 11,12

Now i entered 3 records in toys table for boxID 11 and  assignmentStatus being 'N"
again I entered 1 record in toys table for boxID 12 and assignmentstatus being 'N'

Now i Changed the status of BoxID 12 (ie 1 record exists ) Trigger dint fire.

Now i changed all records (ie 3 records of boxid 11 trigger fired and updated BoxID 11's closedate in ToyBox Table) but not that of ID 12

Is there somethinng missing in ur code posted earlier .

pls let  me know. Thanks in advance
0
 

Author Comment

by:sainavya1215
ID: 12138322
its pretty simple data in both tables

toysBoxTable
ToyBoxID    ToyBoxName    toyOpenDate       NoOftoys     toyCloseDate
1                 ToyBox1          12/02/2004        3                <NULL>
2                 toyBox2           14/02/2004        1                 <NULL>

Toys Table
=========
toyID  ToyBoxID       ToyName    toyAssignmentStatus
1          1                 babydoll       n
2          1                babyDol        n
3          1                babydol        n
4          2                computer      n

if u can see the data ToyBox Table  ToyID 1 has 3 toys which exist in toysTable and ToyBox2 has only 1 toy which exists in toy table too

when status of All Toys for a particular ToyBoxID are changed in toys Table then toyclosdate in toybox table has to be updated with GetDate()

thats it . Hope this helps thanks in advance

0
 
LVL 10

Accepted Solution

by:
imrancs earned 2000 total points
ID: 12140525
sainavya1215, here is complete code for the table structure and data you posted in last post. I have tested it, its working fine.

WARNING: I would suggest to test it in separate database becuase this table has drop table statements, otherwise remove the Drop Table and Create Table Statements from the code and just use the Create Trigger Statement


if object_id('ToysBox') Is Not Null
drop table Toysbox

go

if object_id('Toys') Is Not Null
drop table Toys

go

if object_id('tgr_Toys') Is Not Null
drop trigger tgr_Toys

go

create table ToysBox(ToyBoxId int Identity(1,1),  ToyBoxName varchar(100), ToyOpenDate datetime default GetDate(), NoOfToyes int, ToyCloseDate datetime)
create table Toys(ToyId int Identity(1,1), ToyBoxId int, ToyName varchar(100), ToyAssignmentStatus char(1) default 'N')

go

create trigger tgr_Toys on Toys
For Update
AS
BEGIN

declare @BoxId int

select @boxid = ToyBoxId from inserted

--Check if Status is updated
If Update(ToyAssignmentStatus)
  Begin
       --Check if there no Toys with Status 'N'
       If Not Exists(Select * From Toys Where ToyAssignmentStatus='N' And ToyBoxId = @BoxId)
            Update ToysBox Set ToyCloseDate = GetDate() Where ToyBoxId = @BoxId
             else
                        Update ToysBox Set ToyCloseDate = NULL Where ToyBoxId = @BoxId

            
  End

END

go

insert into ToysBox Values ('ToyBox1', '02/12/2004', 3,NULL)
insert into ToysBox Values ('ToyBox2', '02/14/2004',1,NULL)

insert into Toys(ToyBoxId, ToyName) Values(1, 'babydoll')
insert into Toys(ToyBoxId, ToyName) Values(1,'babyDol')
insert into Toys(ToyBoxId, ToyName) Values(1,'babydol')
insert into Toys(ToyBoxId, ToyName) Values(2,'computer')

update Toys Set ToyAssignmentStatus = 'M' Where ToyId=4
select * from ToysBox



Imran
0
 

Author Comment

by:sainavya1215
ID: 12150217
Excellent thanks a lot
0
 
LVL 10

Expert Comment

by:imrancs
ID: 12151412
Glad if I could help.

Imran
0
 

Author Comment

by:sainavya1215
ID: 12166706
Imran,

How do i integrate error handler RaiseError in the trigger mentioned above ..Thanks in advance
0
 
LVL 10

Expert Comment

by:imrancs
ID: 12166912
what type of error you wanna trap?

0
 
LVL 10

Expert Comment

by:imrancs
ID: 12166971
It should be same like as you [may] did in your Stored Proecedures

place this code code after the every statement for which you want to handle error.

Set @MyError  = @@ERROR
If @MyError <> 0
Begin
   GoTo ErrorHandler
End

and  at the end of the Trigger/SP put this code


ErrorHandler:
 --here code for RaiseError



Imran

0
 

Author Comment

by:sainavya1215
ID: 12290917
hi Imran,
Sorry about the late response. I was on leave... Can we have error handlers after If Exists(seelct *) statements .........I saw many examples where they dont use error handling after if Exists statements why so. could u pls check out below :

does it mean like this  

create trigger tgr_Toys on Toys
For Update
AS
BEGIN

declare @BoxId int

select @boxid = ToyBoxId from inserted

--Check if Status is updated
If Update(ToyAssignmentStatus)
  Begin
       --Check if there no Toys with Status 'N'
       If Not Exists(Select * From Toys Where ToyAssignmentStatus='N' And ToyBoxId = @BoxId)
         if @@error!=0
                  begin
                          return -101
                  end
                            else
                                    begin
                                           return -102
                                    end
            Update ToysBox Set ToyCloseDate = GetDate() Where ToyBoxId = @BoxId
                       if @@error!=0
                             begin
                                     return -103
                              end
                                          else
                                                   begin
                                                             return -104
                                                   end
           else
                    Update ToysBox Set ToyCloseDate = NULL Where ToyBoxId = @BoxId
                               if @@error!=0
                                     begin
                                                return -105
                                        end
                                                 else
                                                          begin
                                                                  return  -106
                                                           end

         
  End

END
0
 
LVL 10

Expert Comment

by:imrancs
ID: 12294929
>>I saw many examples where they dont use error handling after if Exists statements why so.

Well, the reason is, we only check for error after a statement if we know it may cause some error. There are many statements, you can say that these will never cause an error, so there is no need to check for error  after those statements.

Do you think that "If Exists(seelct *) " may cause an error and you have to handle it ?


Just check for error after the statement for which you have doubt that there may be a case when it produce an error.



Imran
0
 

Author Comment

by:sainavya1215
ID: 12302310
How can we know which one would cause an error??? An error can occour any time at any statement right. How can we say that error may not be caused for many  many statements.. Just wanted to know that. @@error specified in doc's say that after each and every sql statement it has to be used. isnt  if exists(select * )  statement cause error? Just wanted to make sure how one can know which one causes and others dont .thanks in advance.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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.

715 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