Tag Info

New answers tagged

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.


0

Unfortunately, no. MySQL documentation clearly states applicable events: trigger_event: { INSERT | UPDATE | DELETE }


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


0

Aha! I've found the answer. Talk about an edge case. First, I found this post from 2007, where someone says: ...the AFTER trigger for synchronizing the CTXCAT index on [column] is not firing (since my update statement does not include the indexed column). ... Oracle, would it not be better to generate the CTXCAT trigger to examine the ...


0

This depends most likely on how your updates will be accomplished, because " A trigger is fired for any affected row once. A rule manipulates the query or generates an additional query. So if many rows are affected in one statement, a rule issuing one extra command is likely to be faster than a trigger that is called for every single row and must ...


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


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

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


0

I'm doing a fair amount of guessing here, but this is how I imagine that your model should look like. In a couple of tables I removed id columns to make it clearer, you may want to put them back in case the keys I have chosen is not stable enough: CREATE TABLE `users` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(100), `password` ...


0

Found_it is null when declared. If no row matches the query that looks for album and userid found_it is not updated, i.e. it is still null. The test found_it!=1 (use found_it <> 1 instead) therefor evaluates to NULL ( NULL <> 1 => NULL ). You can prevent this by a default constraint in your declaration: DECLARE found_it INT DEFAULT 0; Now the ...


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

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



Top 50 recent answers are included