Hello, i have this problem:
The ride promoter cannot make requests to his/her ride repetitions, i.e., the user in the
request (ID_USER) cannot be the same that is referenced by the ride being requested
(car owner). Also, the request must occur before the repetition date. (INSERT/UPDATE)
I need to create a trigger, but i'm not sure what type of trigger i need to crea.t
These are the columns:
Anyone have any tip?
thanks
My tip is: don't use triggers
I really think the requirements that are given shouldn't be in the database but it the app that is using the database.
If you really want triggers then you are writting down the answer your self:
1: The ride promoter cannot make (=BEFORE INSERT TRIGGER) requests to his/her ride repetitions, i.e., the user in the
request (ID_USER) cannot be the same that is referenced by the ride being requested
(car owner)
2: Also, the request must occur before the repetition date. (BEFORE INSERT TRIGGER / BEFORE UPDATE TRIGGER)
How to create a trigger?
CREATE TRIGGER (Transact-SQL) - SQL Server | Microsoft Docs
Why you shouldn't:
Ok... so let's say that you convinced the OP to not use a trigger. What then? How would YOU solve the problem without one? And, remember... putting the logic in the APP may not be in the cards and certainly wouldn't prevent direct entries in the database by someone using a little T-SQL to "make a change".
In your situation triggers will be needed. The question is where to put the business logic? I really think that the app should do this kind of validations as an opportunity to the user to correct the data and try again. But maybe the siuation is to complex and it most be done on database level.
As Iur asked for a tip I shared my thoughts about it besides answering his question of what type to use.
I'm not a front-end Developer and so I have a favor to ask...
If you do know of a way to enforce this business rule quickly on the webserver or even on the client side, then as a non-frontend programmer, I'd sure like to know what the functional flow on that would look like so that I could actually recommend such a thing to get that kind of front-end business logic out of triggers and off the database.
To be sure, I'm not being ironic here... I'd really love to know because this type of thing seems to come up a lot. If you don't have the time, I totally get that. Either way, can you take a quick look at my thoughts below, please, and let me know if I'm off base in my thinking or not? Again, I'm not a front-end Developer and I'd really like to get the skinny from someone that is. Thank you for your time.
Here's my probably incorrect thinking about this and why the trigger seems (to me) to be a necessary evil here...
I'm thinking this isn't a nice simple little business rule that can be calmly and quietly enforced by the App. I'm thinking that it's going to have to do all the same work that a trigger would have to do except it's going to have to do it across the network from the webserver to the database server and still hit on the database server unless there's some serious caching on the webserver and then I wonder how you'd make such a lookup fast and with low resource usage and without having to have a huge amount of memory in the webservers. And, then there'd also be the need to keep such cache in sync, correct?
OR, are you saying that the front-end ALREADY has this data (the ID_User and the ID_Ride for both the REQUESTS and the RIDES tables before either insert and it's just a wicked simple equality check in the front-end code? (In which case, a trigger would actually be a stupid waste of database server time and resources).
Again, thank you for any help in understanding here.
What is the requirement? I am a full stack developer (master of none)
The requirement is what the OP wrote in his original post... prevent writing to REQUESTS table if the ID_User and ID_ride for that insert exist in the RIDES table with an extra check for a date in the REPETTIONS table.
Or at least that's how I interpret what the OP wrote.
actually this is a great and common (sometimes contentious) discussion between DBAs and mid tier developers. The trend is nowadays, in my own experience, specifically when it comes to .net mid tier, a lot of the business logic is done in the middle tier via ORM such as entity framework etc and LINQ. I think this is a great discussion to have here without dogma towards either stack
That's precisely why I'm asking... It IS a very contentious subject and the only way I can make it less contentious is to ask how it would be done on the front-end so that I know and can present both options.
So, how could the OP's request be done on the front-end in an efficient manner taking both the WebServers and the Database Server resources into account? Once I know that, then I could begin to understand when someone should do such a thing on the front-end rather than using something like a trigger on the backend.
Either way, there's still a serious advantage to doing it with a trigger... it would solve the issue no matter if it were multiple different parts of the application that needed such a check (although it could be modularized in the front-end code) and it would also guarantee that the rule(s) would be enforced if someone were using a different app or doing data manipulation from the backend without the benefit of a front end.
@lur ,
While we're waiting, we can work on solving it with a trigger. Do you still need help on this? If so, we need a little more information (or, at least I do).
To be specific, are you saying that a row already exists in the both the Rides and Repetitions tables and that your trying to insert a for that contains the ID_Ride of those existing rows into the Requests table and that's when you want to make the check using the trigger?
You would not do it on front end. But in the mid tier.
For ex via traditional rest api or the newest graphql
So in mid tier service you could call a stored procedure or via EF verify that it meets requirement
Would need to know what his mid tier is written in if op's software has any. It could be written in access vba or even excel
Ok. Just for grins, let's say the mid tier service was written in EF. EF would have to hit the database a minimum of 3 times, correct? Once for the Rides table info and once for the Repetitions table to get the data and then if everything worked out correctly, then once more to insert into the Requests table, correct?
And a stored procedure would have to do the same thing, correct?
Depends on arch design such as caching etc. But suffice to say with ef u could do one read and one insert. So 2 db calls via ef.
How many for a trigger?
One from the mid tier. It would just require the insert. Internally, the trigger would work a bit like a stored procedure in that it would, if an "after" trigger, check the two tables. If the conditions were correct, that would be the end of it it. If not correct, the trigger would rollback the insert.
In SQL Server, you could write an "Instead Of" trigger to check the data and two tables before doing the insert. If everything is ok, then the insert would happen. If not ok, no insert and no rollback.
I DO wish that MS would build true BEFORE triggers like what Oracle has. Those are SO SIMPLE. "Instead of" triggers in SQL are a PITA, especially for UPDATEs (not applicable here, though).
So, the bottom line sounds like two trips through the NIC if it's coming from the mid tier and it has to do the same work as a trigger or 1 trip through the NIC and do the work in a trigger.
Because of the added benefit that the trigger would also enforce the rules for other apps and non-app procs and human driven inserts on the back end, the trigger wins IMHO.
If caching on the WebServer is done, I'm still thinking the trigger wins because the cache does not need to be kept up to date when using a trigger.
I believe that, unless you have some form of proof as to an advantage of doing all this through the mid-tier without the use of a trigger, I'll have to continue to be "contentious" 
and say the trigger wins. The only thing that would be better is a stored procedure because it would work very much like a true BEFORE trigger and not do the INSERT if conditions were wrong. The disadvantage there is there would be no enforcement of the rule if someone did a direct INSERT using the likes of SSMS or even some other proc.
One good reasoning of doing all things in the mid tier is that it is db agnostic. Triggers and sprocs are specific to certain database products.
How would triggers work in an app that spans multiple time zones countries continents
Heh... That's a little bit like a famous author (who's name I won't mention here) saying that you should only write ANSI SQL just in case you might want to do a database migration someday instead of ever using the powerful proprietary features of many of the relational database engines.. 
Of course, he's the same guy that recommends violating ANSI date formats because he like a certain way of listing a single month as a whole.
And what makes you think that you can't write triggers (or any other code) that operates correctly when spanning multiple time zones?
agree 100% on anybody using the words always or never especially on tech stuff. "just in case you might want to do a database migration someday" does not negate the fact that you might have to do a lot of rewrite. So as you say why miss out on the powerful features.
Hey guys, this was for an academic exercise that i had to deliver and time has expired already.
Thanks anyway for your help.
Regards,
Rui
cool
"academic exercise". So share with us what you've learned and what solution you ended up with.
