Tag Info

Hot answers tagged

2

As it turns out, the key word loop needs to be double-quoted inside PL/pgSQL or the parser is confused: SELECT public.foo_fun(NEW."loop") INTO fRV; The situation in Postgres 9.3 is unchanged: SQL fiddle with quotes (works). SQL fiddle without quotes (doesn't work). It's generally a good idea to avoid typical key words like loop as identifiers. There are ...


2

Yes, use a BEFORE INSERT trigger, and return null. Live example here. create table stuff ( stuff_id int primary key, thing text ); create or replace function stuff_inserting() returns trigger language plpgsql as $$ begin return null; end $$; insert into stuff values (1, 'asdf'); select * from stuff; /* returns 1 row */ create trigger inserting ...


2

This could be done this way. I don't like it much as it is not very elegant and looks fragile if unexpected things happen. --1)Create a table in the user's schema or another new schema if you want separation. CREATE TABLE DATA_AUDIT ( Primary_key RAW(16) default SYS_GUID() not null, Data_id Number(10), Action VARCHAR2(10), Date_created ...


2

SQL server 2005 SP3 which you have as of now(9.00.4035.00 ) is out of support. So first thing you should do is apply SP4. Access violation exception generally means some bug in SQL Server and its best to get this stack dump analyzed by Microsoft Engineer. You might get lucky with debugger but you would not be able to draw correct inference from it. Even if ...


1

No, deferred trigger execution will not help. It only defers triggers until the end of the transaction. PostgreSQL doesn't have a built-in task/job scheduler, either, so you'll need to do this in your app. In the trigger, insert a row into a table that keeps track of which entries should be deleted and when. Scan that table every few minutes and, in a ...


1

Posting this as an answer to address the core question: Is there a way to perform my history insert and then tell SQL Server to continue with the original INSERT, UPDATE or DELETE without writing those myself? No, it is not possible to have an INSTEAD OF trigger just perform the original DML for you. This is why they are called INSTEAD OF and not ...


1

You could just use a FOR trigger instead of an INSTEAD OF trigger. The records, as they were before the DML statement, are still in the deleted table (for updates and deletes). Everything will still be contained in an implicit transaction. In my opinion, INSTEAD OF triggers are really handy when you don't want to run the original DML, or on views.


1

You are defining an ordinary AFTER trigger. So use an AFTER trigger, not an INSTEAD OF trigger. The cases where you want to use 'instead of' triggers are, basically, none.


1

In a PL/pgSQL function (which you are using, but the language declaration is missing), use an EXCEPTION clause in your block. I also consolidated your two SQL statement with variable declaration and assignment into a single, equivalent statement, which is typically much faster and less susceptible to race conditions in a multi-user environment. Details in ...


1

First, drop the triggers you have. There is absolutely no reason to be doing this with multiple triggers. Then: CREATE TRIGGER dbo.FlightInput ON dbo.Reservation AFTER INSERT, UPDATE AS BEGIN SET NOCOUNT ON; UPDATE r SET Depature_Airport = CASE i.flight_code WHEN 'EM0088' THEN 'LDN' WHEN 'MH0007' THEN 'something else' END, ...


1

Your trigger is designed to only fire after an insert not an update. Try changing the code to:- create trigger flightinput2 on reservation AFTER INSERT, UPDATE as begin update reservation set Departure_Airport='LDN',Arrival_Airport='KUL' where Flight_Code='EM0088' end EDIT:- As pointed out, the code above will update all records within the database ...


1

Wouldn't it be better to store logins in a table and then provide a report for that table? Or perhaps, email a report that shows a list of logins for that day. /* create test login */ CREATE LOGIN login_test WITH PASSWORD = '3KHJ6dhx(0xVYsdf' GO GRANT VIEW SERVER STATE TO login_test; GO USE AUDIT GO /* Creating audit table*/ CREATE TABLE ...


1

try this, as per the link in comment, you can creat a trigger and I have added some script to send mail USE master; GO CREATE LOGIN login_test WITH PASSWORD = '3KHJ6dhx(0xVYsdf' GO GRANT VIEW SERVER STATE TO login_test; GO GO CREATE TRIGGER mail_sending_trigger ON ALL SERVER WITH EXECUTE AS 'login_test' FOR LOGON AS BEGIN EXEC msdb.dbo.sp_send_dbmail ...


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 > ...



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