I am need to create a trigger on a table, which should raise error that ' the table should not be updated manually, instead use a store procedure'. I have the same for SQL server and I need to convert that in Teradata.
/****** Object: Trigger [Sysdata_Guardian] Script Date: 03/31/2017 14:25:35 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
instead of update as
raiserror(N'Please do not update sysdata table manually. Use usp_UpdateSysdata SPs instead.', 11, 1)
Can anyone help me out here.
In Teradata you would provide this protection using security, not a trigger. A trigger fires on a DML statement (insert, update or delete) against a table, regardless of how that DML statement is sent to Teradata.
Use Access Rights (i.e. security). Grant the necessary users the privilege to CALL the SP and make sure that they do NOT have UPDATE on the table.
Out of interest, in your SQL server example, how does that differentiate between an UPDATE command coming from the SP and one being executed directly against the table?
Answer to your question:
In the SP, There is a clause which disable the trigger when certain condition satisfy, and after the update It again enable the trigger.
Back to my query,
So, there is now way to impliment this via trigger. Because in many SP we have the condition to enable and disable the trigger and then update the table. We again now have to make changes to all those SP.
Ok, thanks for the info.
You should be able to take the same approach from within your SP, try:
ALTER TRIGGER trigger-name DISABLED;
and then after your update use:
ALTER TRIGGER trigger-name ENABLED;
Just to re-iterate, the usual way of meeting your requirement in a Teradata system is via security, not by enabling/disabling a trigger. I strongly recommend that you look at using that mechanism.
I understand that you didn't want to do that because of changing lots/every SP, but I suspect that you'll have to change them anyway in order to move them from SQL server to Teradata. This would be another change that has to be incorporated into that process.
But it is your choice.
I am done with the enable and disable of trigger in Teradata. All the SP's have been changed to this condition.
What I just need now is to create a trigger for before any manual update on table and it will raise error that "UPDATE IS ONLY POSSIBLE VIA SP". If nothing is possible, I have to take the User access control way, but thay way is quite long.
As per my earlier reply in Teradata the trigger fires based on the DML statement, not how it is sent to the DBMS.
In your requirement below it is the "manual update on table" that cannot be accomplished using a trigger in Teradata. If the trigger is defined 'on update' then it will fire whether the update is sent as a direct 'update table' command by a user or if the update is sent from an SP.
From what you've said about your SQL server SP then you have exactly the same issue there. The trigger itself is not defined for manual update, but any update. It is SP logic that disables/enables the trigger giving you the capability of preventing manual updates.
You can follow the same logic in Teradata - albeit using maybe slightly different syntax.
AFAIK a trigger in Teradata cannot return a message (in your SQL server example you have a 'raiseerror' statement) to the user.
Sorry, but apart from that I'm not sure how I can help further. Let me know.
Just trying to clarify my thoughts on this.
What is the objective of your processing logic?
If that is correct then in a Teradata environment a trigger is not the appropriate object. In Teradata a trigger is not designed to prevent a certain type of access to an object. If you want to do that you use security (Access Rights). Yes that is possibly more work then re-writing an SP, but that is the correct (best?) mechanism in a Teradata environment.
Preventing this type of direct access and forcing users to go via an SP is quite common in a Teradata environment. This allows the SP to then enforce other rules and complete other processing. BUT you don't achieve that using a trigger, you achieve that by using security.
Note that there is a small window while the trigger is (globally) disabled that users could potentially do direct updates. Dave is correct, this should be managed via access rights rather than triggers. It's not clear why it would be a big deal to make that change.
Just for completeness: a trigger can issue an ABORT and an AFTER trigger can also call a SP or UDF - so it may be possible to provide some sort of message text. But I wouldn't waste the effort on developing an "almost" solution.
No, you really shouldn't try to use this technique to block user-initiated updates. It is not only non-standard, it is more cumbersome and less secure than using security privileges. If users should not be doing updates, then do not grant that privilege to any user but the procedure creator.
Even as a practical matter, I do not see anything corresponding to instead-of in Teradata triggers. Did you read the section on Triggers in the SQL DDL manual from info.teradata.com? However, if there were a way to stop an update, then a trigger could either SIGNAL (or call a stored procedure that would SIGNAL) an exception with a message, and the user would see that.