Macros in Access 2016 database triggers in access database

okay today we are going to talk about macros as trigger in Microsoft Access database okay so trigger basically is something which causes based on a event another event okay one even takes place and then another event happens due to that that the second event is happening due to the trigger okay trigger is a program kind of thing which enables some other action to take this based on previous action that has been taken place so in Microsoft Access it's not called as trigger but it is called as data macros so that's why we are going to see in the MS Access database but in other databases like sequel our DBMS database is like my sequel Oracle sequel server it's called as trigger but in Microsoft Access it's called as Metro okay so we discuss about two macros and we will see them through example okay how to create it so first one is after insert rigueur macro the question here is create a data macro associated with the after insert event okay so the question is after insert event for the reservation table to increment the previous trip field or the appropriate customer so what it's saying and what is saying is we have our reservation table and we have a customer table okay he's saying that now both of these tables have a common column called as customer number okay the reservation table is having the reservation details and customer table is having the customer details okay so in customer table customer num is primary key in reservation table the customer num is the foreign key okay so what it's saying is for any new record inserted in reservation table go to the customer table and increase the value of previous trip okay that's what it's saying increase the value of previous trip paint or the appropriate customer when inserting your own reservation table so that's what it's saying so we have to write a trigger that will do this so let's open our access let's see our data again we this trip column is here in the customer table and we have the customer namastey first column okay so 1 1 1 or 2 these are customer number and biggest your values you can see so we have to go to the reservation table and what he is saying is when we insert a row say for example let's insert a row let's take a note of what we have right now so the value of the previous trick column for first one is eight okay it's eight here and see that and the customer number is 101 okay let's go to reservation table and let's try to add one more record for 101 okay so you see we already have a trigger return reservation table has a trigger after insert and after delete so the artery insert is supposed to do what we are going to write now but it's already there so and do a demo first how it works and then I'll show you how to write it okay so let's insert one record in the reservation table I'm trying to show you what the question is okay the question is when you are inserting a record something like this customer number or say 101 okay the value was 8 right we're gonna see that it increased to 9 now I entered a record and you will see that it increase to 9 okay now it's 9 we'll do one more and we'll see it should turn to 10 so that's the question write a trigger to do something like that same customer number one zero one it should become ten C okay that is what is our train sir trigger now let's run learn how to write it so go to the table on which the trigger event happens okay the first event where ever happens on that table go go to that table click on the table name make sure that table is highlighted in this pink color in this max s or whatever color it is but it should be highlighted and on top click on table okay maybe if you click on the wrong tab on top it will not show so go and click on top table and then you will see all these table things so you'll go and select the after insert right now we already have it so we gonna delete it and we gonna write it again okay so we learn how to delete it go to the name macro rename or delete and we're gonna do after insert will divert okay we deleted that so now nothing's there again click on the table name click on table table and then click on after insert okay so how do we write it we already get we get like this kind of window and we start writing it inside that window okay it's like basically give you drop-down things and you're just fill in some information so once the record is inserted into the reservation table what we have to do we have to get the value of that customer number whatever got inserted and find the corresponding record in the customer table okay from reservation table get the customer number use that customer number to find our customer in the customer table so what we do is first look up a record in customer table so here you see lookup record and look up record we're in customer table okay it will show up in drop down all the tables you have and then what's the where condition here the work condition a is customer dot customer number should be equal to distribution dot customer number so based on this where Clause we're gonna find the recording the customer table that needs to be incremented whose previous music implemented so within this one itself now you gonna like that this plus because it's inside that even once you find it what you need to do so you need to so what I did was I went to this plus click that plus and then it opens one more condition inside that one only one more drop down and now once we have found the record what we do we start editing the record okay we have to ready to the record now editing the record will be a two-stage process first you have to save the existing value okay so it should not be that field it should be get rid of that it should be set local variable okay first you saved our existing value in a variable called a stamp existing value of what existing value of customer dot previous strip once save it in the temp table once your temp variable and then you can go in set the field field is customer customer dot previous trip we have to increase that by one right so we save the value in term and we increase it by one because all we needed to do okay this is what is the world okay so I just deleted this and wrote it one more time and then that ice Commission thing is gone okay so we save it we have done it so we save it at me close you already have tested it before so we'll test it again okay let's go and pick one customer number so the second one is three right we'll increase that second guy 1:02 we add a reservation for 1 0 2 so 1 0 2 so 1 0 2 was 3 right it becomes 4 now okay that's how the offerings are trigger works okay now the next question is let's discuss about after delete trigger okay after the new trigger is something opposite okay but one thing to note in after trigger is when I record gets deleted only then the event takes place okay so if something you have to do with the deleted record the data is already lost okay so the access provides and other database is also provided by our field or temporary table or something like that you can call whatever you want but you can get the deleted data by using keyword called as old okay we will see that in example so here the question is cleared the macro so that when yeah so the question is after the lady went on the reservation table to subtract one of the record being deleted from customers previous trip total test the data okay so what is saying is you have to do a delete trigger to do the exact opposite so go to the reservation when you delete one record it should correspondingly come to the customer table and delete renews the previous trip value from now one by one so right now it is stand ok if you go and delete the one four one zero one say this one is for one zero one right so right now the previous trip value is ten so what if we delete it it should become nine okay so let's go ahead and delete it by I'll just click on cut to delete that I committed it go delete one record let's delete it and let's go and check in customer what happened so I became blind see that's fine at a time so you can delete one more it will come eight let's see that so this one is one zero one that's the get one zero one one you you can delete by writing a delete statement okay and even in previous example you can write by insert access provides the option to write here directly also to leave this and go in check eight if you try the same thing with 1:02 customer here score okay to become three so 1:02 customer is the record we cannot delete it okay so that's how that after the you trigger works now let's write it okay you saw the demo so processes click on the table where the trigger needs to be written it's going to be written on the reservation table because the first action happens on the reservation table which is deleting of a record we're gonna table then click on the word table on top and then click on out to delete okay now it's highlighter it means there is already already a macro there okay so what we gonna do is we gonna deal with that one what is it so now it's deleted so let's click on not to delete and we can start creating one here okay so in the reservation table what are we gonna do the record has been deleted so the record is deleted we gonna follow the same process find the record in the customer table which for which the previous strip value we need to reduce so how we find it same process we do the lookup record okay where do we look at the record in the customer table that's correct and what is the condition condition would be customer dot customer number equal to the preservation okay but the record is already deleted if you do this you won't get the data okay so what the access to device is the key one called as old you use the word old then you can create the one which got just deleted so old well the record that God just deleted will be getting old and then old our customer name will give you the customer name of the record in the record whatever the value was for the customer name number that will that you will get using that you can find the record in the customer table whose previous trip you have to reduce okay so this way you will find the recording the customer table what this will do it this will give you the recording the customer table once you got that so inside the gray are grayed out portion only you to start next will okay don't start in the next one because it's one or one operation so that will be for the next one another trigger so here we have to now where to do our action what we need to do is we need to update data right so what we will do here is we will select edit edit record and how we did record first we will save the existing value and then we'll declare my decrease or decrement by one so what we do is set a local variable and again we can call it a local variable a stem and the expression value of that will be customer dot you guess trip okay so that's the value we are saving in term and once we have that value in the temp we will set the field field is like table table column so what will go to certain thing customer dot the district we have to set that value toward time minus 1 because we are reducing by one hit enter and we don't see any red mark anywhere so we can save it and we will close it after take trigger we wrote there so what we have to do is now is we have to test so we'll the lead will try to give you the let's see who has many believe that one so I think the first one itself has more eight record okay second one will try to delete the one with three two records here so which was the customer number for this personal customer number is 1 0 4 and previous trip is - ok we will try to delete the one with 1 0 for customer number in reservation table ok here is 1 1 0 for 1 only one record anyways will give me that one zero for okay it's - all right now for 1 0 for values do we expect it to become one okay so 1 0 for you me the right to delete key or highlight like this and it on cut okay we are going to delete the 1:04 say yes gone now you see 1 0 for us become one okay I mean let's try 1 0 1 which has eight records it should become 7 so where is 1 0 1 it don't have one - the one you get it off once it once let's select 1 0 2 only which is 3 right we have 1 0 2 or top they cannot agree that and what we expected to become x3 normal to become too so let's go to reservation I'll I do it and click on current say yes and customer become - okay that's how the up to the heat after delete trigger work macro so again how do we see if it is there or not go to the table on which the microwaves click on top table and you'll see those are highlighted after insert is there an opt-in to take that and see the definition by clicking them and can review it okay and you can change you cut all you want ginger you can write another trigger here

Channel: SharedTopics

Views: 1,714

Rating: 5 out of 5

Keywords: msaccess, ms access, access, triggers, macros, macros in access, triggers in access, after insert trigger, after delete trigger, access 2016, ms access 2016, MS Access 2016 - Create an Event-Driven Data Macro, MS Office, Computer Fundamentals

Id: sFs5WMbT1q8

Length: 16min 35sec (995 seconds)

Published: Fri Apr 03 2020