Solved

Trigger to be written with 2 Tables

Posted on 2004-09-23
18
251 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
  • 8
  • 7
  • 2
  • +1
18 Comments
 
LVL 10

Expert Comment

by:imrancs
Comment Utility
please explain your problem with some example data.


Imran
0
 
LVL 10

Expert Comment

by:imrancs
Comment Utility
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
Comment Utility
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
 
LVL 2

Expert Comment

by:vidnan123
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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 500 total points
Comment Utility
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:sainavya1215
Comment Utility
Excellent thanks a lot
0
 
LVL 10

Expert Comment

by:imrancs
Comment Utility
Glad if I could help.

Imran
0
 

Author Comment

by:sainavya1215
Comment Utility
Imran,

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

Expert Comment

by:imrancs
Comment Utility
what type of error you wanna trap?

0
 
LVL 10

Expert Comment

by:imrancs
Comment Utility
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
Comment Utility
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
Comment Utility
>>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
Comment Utility
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
 

Author Comment

by:sainavya1215
Comment Utility
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

762 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now