Tag Info

Hot answers tagged

3

something like below should get you started ... CREATE TABLE [dbo].[patientInfo] ( id int, patientid int, starttime datetime, endtime datetime, doctorid int ) GO ALTER TABLE patientInfo ADD expire AS CASE WHEN endtime < GETDATE()THEN 1 ELSE 0 END GO declare @now datetime=getdate() INSERT into patientInfo (id, patientid, starttime, ...


3

Couple of Options: Option1: Your best option is to run a server side trace with filtering options enabled. This way you can narrow down the amount of data captured. Option2: Depending on the version (Enterprise), you can use SQL Audit as it allows you to track DELETE, EXECUTE and other Actions on the Object level as well. So you can filter the table ...


2

Why is t_history.id auto-incremented in the first place? If "both t and t_history have the same schema", and t.id is a serial PK, you can just copy whole rows. I would also suggest you only copy rows you actually delete from t to t_history - in a data-modifying CTE. This way you do not have overlapping rows (which might be part of the problem). CREATE ...


2

A partial answer to your question, could be to use DBCC INPUTBUFFER, this doesn't necessarily give you the stored procedure that is deleting your records. It can give you the first stored procedure called by the client, ie: application code calls: EXEC EntryStoredProc /* this call is the one that will be identified by DBCC INPUTBUFFER */ ... /* inside ...


2

Because update trigger is invoked once per row being updated, it is not invoked at all if no rows are going to be updated. See for yourself: create table test(i int); create view test_view as select i from test; CREATE OR REPLACE FUNCTION test_trigger_function() RETURNS trigger AS $$ BEGIN RAISE NOTICE 'hi'; RETURN NEW; END; $$ ...


1

Triggers in SQL Server are fired once per set, not per row (like Oracle), so you need to build triggers that handle this. In all triggers, you have two special tables called inserted and deleted, that contain a virtual "mirror" of the entire table row(s) after and before the action that triggered it, respectively. A word of warning: triggers should be ...


1

Based on CL's suggestion to take another look at last_insert_rowid() and now that it's clear that INSTEAD OF triggers are only for VIEWs (see question's comments), here's a working version: -- We need a view to create the trigger on CREATE VIEW Container_Auto AS select * from Container; -- Since it's automatic we need to check if we need to create a root ...


1

With regard to SUPER and TRIGGER privileges, you need the TRIGGER privilege to create triggers on tables you have rights for. If you play binlogs, there is the possibility of seeing commands to create triggers on tables you do not have rights for. This is implied from the MySQL Documentation: CREATE TRIGGER requires the TRIGGER privilege for the table ...


1

The fragment you posted so far can be simplified to: INSERT INTO table2 (id, name, date) -- why "date" if you insert a timestamp? SELECT NEW.id, t1.name, NEW.timestamp FROM table1 t1 WHERE ST_DWithin(NEW.position , ST_SetSRID(ST_MakePoint(t1.longitudedecimal, t1.latitudedecimal), 4326) , 0.01447534783) AND t1.id > ...


1

Use a before update trigger. Do you want to raise an exception or just have it silently not change the value? Yes, you should return NEW. Yes, you should prefix the column name with NEW or OLD. You can use this if you don't want to raise an exception: create function check_id_change() returns trigger language plpgsql as $$ begin new.original_id = ...



Only top voted, non community-wiki answers of a minimum length are eligible