New answers tagged

0

The only thing I can think of is to create a on delete trigger on each table to raise an exception or soft-delete (e.g. set a deleted column to true). I don't think there is a way to revoke a privilege to an object created by the schema owner--I couldn't get it to work on Oracle. If rows cannot be deleted, then the database files won't shrink. Of course, ...


2

We have an application, with an application user needs to be db_owner on the database If this application is written "in-house" then push back - hard - and get them to justify why this needs to be the case. Running an application as a database owner, you might as well hand the database back to the Development team because they can do pretty much ...


1

In addition to the other answer and comments: Your constraint will give you an error message and halt the operation, and the trigger will never fire. Constraints are checked before triggers are fired. So if you really want to do this then either have a trigger or a constraint. You can of course have a trigger and a disabled constraint (letting the constraint ...


2

The problem you originally had was that you were not referencing the deleted pseudo-table. You were essentially saying "Oh, delete subcategory 5? Ok, delete all products that have a subcategory!" I think you want this in your trigger: DELETE p FROM dbo.DimProduct AS p INNER JOIN deleted AS d ON p.ProductSubcategoryKey = d....


0

I would recommend against using a trigger as a solution. In general, if you are going to INSERT, UPDATE, or DELETE with a trigger, you will can experience problems with concurrency, data quality, and maintainability. I recommend you read this Ask TOM article. Consider the following scenarios: One more rows in the traffic_shift table are updated. You will ...


0

No SUM'ing required here - use the Triggers keep things up to date as you go along. Create Insert and Update Triggers on traffic_shift to update traffic_daily with the values given to the trigger: update daily set car_return = car_return + new.car_return - old.car_return , car_single = car_single + new.car_single - old.car_single where day = new.day ...


2

I fear the where clause with the in is causing problems. Try joining to the inserted table instead. Here are two examples - one with the in clause and one with a join to the inserted table. The results are different using my sample data. drop table if exists shipment; drop table if exists alphauniversal; go create table SHIPMENT (cm_controllerID int,au ...


3

(Edited following comments and after checking what INSERTED is in SQL Server) The INSERTED table in SQL Server represents the rows that were inserted by the triggering statement. Oracle doesn't have this. Instead, a row-level trigger can refer to the column values of each row affected by the trigger, using :new and :old values. (Only :new values are ...


1

I would recommend using this trigger template where possible. change it to suit your needs, of course. It uses an IF UPDATE(column) to short circuit if the DML doesn't reference the column the trigger cares about. It also uses the EXCEPT clause for what I've found the fastest way to compare the INSERTED and DELETED tables. Just add more columns to the ...


0

This is the trigger that I ultimately ended up using: create or replace trigger V202577_DELETE_custom before insert on D202577 for each row declare v_upd_row number; v_cre_date_row number; begin select count (1) into v_upd_row from A202577 where objectid = :new.sde_deletes_row_id ...


1

If I understood you correctly, then see if this helps: select created_date from that table check if it is lower than previous Sunday's 23:59 why? Because I presume that it can't be created in the future How to find that moment in time? One option is to use NEXT_DAY function which returns the "next" Sunday, but - if you subtract 7 (days) from it, ...


Top 50 recent answers are included