Скачать или смотреть Restrict Creation of Tables on Sundays in Oracle

Discover how to use PL/SQL triggers in Oracle to prevent table creation on Sundays. This comprehensive guide walks you through the steps and provides troubleshooting tips.
---
This video is based on the question https://stackoverflow.com/q/68067302/ asked by the user 'Harsh Kairamkonda' ( https://stackoverflow.com/u/9602037/ ) and on the answer https://stackoverflow.com/a/68067856/ provided by the user 'Littlefoot' ( https://stackoverflow.com/u/9097906/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Restrict creation of table on sunday in oracle

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Restrict Creation of Tables on Sundays in Oracle: A Complete Guide

Creating database tables is a routine task for developers, but what if you want to impose some restrictions on this process? Specifically, if you're using Oracle and want to restrict the creation of tables on Sundays, you need a reliable solution. In this guide, we'll walk through a method using a PL/SQL trigger to achieve just that.

The Problem: Preventing Table Creation on Sundays

The problem is straightforward: You need a mechanism in your Oracle database to prevent any user from creating tables on Sundays. However, if you attempt to create a trigger for this purpose based on system events, you may encounter several complications.

Example of an Attempted Solution

An example of an initial attempt might look something like this:

[[See Video to Reveal this Text or Code Snippet]]

Unfortunately, this approach can lead to errors such as ORA-30506: system triggers cannot be based on tables or views. Thus, a new strategy is needed.

The Solution: A Working Trigger

The recommended solution involves creating a trigger that prevents any user, say "scott," from creating objects on a specified day of the week. Here’s how to effectively implement this trigger:

Step 1: Connect as SYS

Make sure you are connected as a privileged user, like SYS, to create the trigger. You can do this by executing:

[[See Video to Reveal this Text or Code Snippet]]

Step 2: Create the Trigger

Create a trigger that will check what day it is whenever someone tries to create a table. If it’s Sunday, the trigger will raise an error and halt the process:

[[See Video to Reveal this Text or Code Snippet]]

Step 3: Test Your Trigger

To ensure that your trigger is working correctly, you can perform the following tests.

Verify the Current Day: To check which day it is, you can run:

[[See Video to Reveal this Text or Code Snippet]]

Attempt to Create a Table: Try creating a table while it is Sunday:

[[See Video to Reveal this Text or Code Snippet]]

You should receive an error: ORA-20000: You cannot create any objects on Sunday.

Step 4: Testing for Other Days

Pretend it’s a day other than Sunday, and ensure that table creation works fine:

For example, if today is Monday, adjust the system’s date (for testing) and check:

[[See Video to Reveal this Text or Code Snippet]]

Then, attempt to create a table again:

[[See Video to Reveal this Text or Code Snippet]]

This should work, and you’ll see the message "Table created."

Conclusion

By following these steps, you can effectively restrict the creation of tables on Sundays in an Oracle environment. This approach not only showcases the flexibility of PL/SQL but also gives you the control you need over your database operations.

If you encounter any errors or need further assistance, feel free to explore the error messages and leverage Oracle's documentation.

Now that you know how to prevent table creation on Sundays, take a moment to implement this in your own database environment. Happy coding!