This week's book giveaway is in the Jython/Python forum.
We're giving away four copies of Hands On Software Engineering with Python and have Brian Allbey on-line!
See this thread for details.
Win a copy of Hands On Software Engineering with Python this week in the Jython/Python forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Bear Bibeault
  • Knute Snortum
  • Liutauras Vilda
Sheriffs:
  • Tim Cooke
  • Devaka Cooray
  • Paul Clapham
Saloon Keepers:
  • Tim Moores
  • Frits Walraven
  • Ron McLeod
  • Ganesh Patekar
  • salvin francis
Bartenders:
  • Tim Holloway
  • Carey Brown
  • Stephan van Hulst

Trigger to update the user type  RSS feed

 
Ranch Hand
Posts: 216
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,


I have a requirement , where when the user_type is set as 100 (from 200) for a user_id = 1003 I need update the user_type back to 200. I need to create a trigger which monitor this record (only single user whos id is 1003) and see whether the user_type is 100 if so immediately update it to 200. Please let me know how we can create a trigger with update table so that immediately when this user_type is set as 100 through trigger we need to update it to 200 for the user_id 1003.


update customer set user_type = 200 where user_id = 1003;


we need to add this in trigger,  so thinking to go with a "before update trigger"  to make sure the user_type is set as 200 something like this . There is a push to implement this through trigger. Please let me know whether the below is correct?



CREATE OR REPLACE TRIGGER CUST_ID_TRIGGER BEFORE UPDATE ON CUSTOMER BEGIN SET USER_TYPE = '200' END;

Thanks
 
Saloon Keeper
Posts: 5124
135
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Running the trigger before the update doesn't accomplish anything, because at that point the value is 200 anyway, no? The trigger should  run after the update (or otherwise just cancel the impending update).

It goes without saying that this kind of logic should not live in the DB layer, but somewhere in the application layer, but that's not always possible due to several different kinds of clients accessing the DB.
 
Rithanya Laxmi
Ranch Hand
Posts: 216
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks Tim, so we can write a after update trigger to handle this?  we cant cancel this UPDATE getting executed as we are not sure from which app this is getting triggered as this is a common table used by multiple applications.

CREATE OR REPLACE TRIGGER CUST_ID_TRIGGER AFTER UPDATE ON CUSTOMER BEGIN SET USER_TYPE = '200' END;
 
Rancher
Posts: 3794
40
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Depending on the database, add a CHECK constraint to the table. Something like:


That way the problematic app is the one that gets the hit and can then be fixed.
 
Rithanya Laxmi
Ranch Hand
Posts: 216
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks Dave, so i can do something like this :-


ALTER TABLE CUSTOMER
ADD CONSTRAINT CUSTOMER_USER_TYPE
 CHECK (user_id = 1003 AND user_type = 200);

In this case for the user id 1003 if the user_type is getting updated to any other value other than 200 , then it will thrown an constraint error? and this constraint check wont have any impact for other users "user_types" where there should not be any impact on those user types? please clarify.
 
Dave Tolls
Rancher
Posts: 3794
40
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
No, you have to add the OR part as well, otherwise only the user_id 1003, with a type of 200, will ever be allowed to be added or updated.
 
Dave Tolls
Rancher
Posts: 3794
40
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Also, once you know which app (or apps) that are causing th eproblems, then hopefully they'll fix thngs, and then you can disable the CHECK.
 
Rithanya Laxmi
Ranch Hand
Posts: 216
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks a lot Dave, so in this case it is better to use Check constraint than Trigger? also as you mentioned the check constraint usage wont impact any of the existing user type values for other user_ids, i think it is better to use this approach than trigger? please clarify as the client is asking us to make use of Trigger? not entirely sure the reason if we can do the same via simple SQL statement? please advise.
 
Dave Tolls
Rancher
Posts: 3794
40
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It depends on your client.
My preference in a situation like this is that you have an app out there doing something they shouldn't be doing (changing this user_type for this user), so they should end up getting an error.

Using a trigger will hide this issue.
Using a constraint will highlight it to the app making the problematic UPDATE query.

Now, if the client is adamant that they cannot (or will not) make changes to their app, then your only option is a trigger to "fix" the issue.  But I would consider that a bit of sticky tape over the problem, rather than an actual fix.

Obviously, bear in mind I do not know your requirements, or service deals or anything like that.
I'm simply taking this from the position that something is setting user_id 1003's user_type to something other than 200, and it shouldn't be.  It's quite possible (even likely) that there are other things in play here that I do not know about.
 
Rithanya Laxmi
Ranch Hand
Posts: 216
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks you so much Dave, really appreciated all the inputs you have provided.
 
Not so fast naughty spawn! I want you to know about
RavenDB is an Open Source NoSQL Database that’s fully transactional (ACID) across your database
https://coderanch.com/t/704633/RavenDB-Open-Source-NoSQL-Database
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!