How to get the line of code that triggers a query?
I've found this solution:
module QueryTrace
def self.enable!
::ActiveRecord::LogSubscriber.send(:include, self)
end
def self.append_features(klass)
super
klass.class_eval do
unless method_defined?(:log_info_without_trace)
alias_method :log_info_without_trace, :sql
alias_method :sql, :log_info_with_trace
end
end
end
def log_info_with_trace(event)
log_info_without_trace(event)
trace_log = Rails.backtrace_cleaner.clean(caller).first
if trace_log && event.payload[:name] != 'SCHEMA'
logger.debug(" \\_ \e[33mCalled from:\e[0m " + trace_log)
end
end
end
In some initializer add QueryTrace.enable!
SQL Server: How to find what lines are executed
So the extended events are the solution, this is how I have done it:
IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='testMSSQLTrace')
DROP EVENT SESSION testMSSQLTrace ON SERVER;
DECLARE @cmd VARCHAR(MAX) = '';
SELECT @cmd = 'CREATE EVENT SESSION testMSSQLTrace
ON SERVER
ADD EVENT sqlserver.sp_statement_completed
(WHERE (sqlserver.database_name = N''' + DB_NAME() + '''))
ADD TARGET package0.ring_buffer
WITH (
MAX_MEMORY = 2048 KB,
EVENT_RETENTION_MODE = NO_EVENT_LOSS,
MAX_DISPATCH_LATENCY = 3 SECONDS,
MAX_EVENT_SIZE = 0 KB,
MEMORY_PARTITION_MODE = NONE,
TRACK_CAUSALITY = OFF,
STARTUP_STATE = OFF
);'
EXEC (@cmd)
This creates an event that can be fired after every statement completion, this is done dynamicly to filter on the database
Then I have 3 procedures that make controlling this event easy
/*******************************************************************************************
Starts the statement trace
*******************************************************************************************/
CREATE OR ALTER PROC testMSSQL.Private_StartTrace
AS
BEGIN
ALTER EVENT SESSION testMSSQLTrace
ON SERVER
STATE = START;
END
GO
/*******************************************************************************************
Ends the statement trace, this also clears the trace
*******************************************************************************************/
CREATE OR ALTER PROC testMSSQL.Private_StopTrace
AS
BEGIN
ALTER EVENT SESSION testMSSQLTrace
ON SERVER
STATE = STOP;
END
GO
/*******************************************************************************************
Saves the statements trace
*******************************************************************************************/
CREATE OR ALTER PROC testMSSQL.Private_SaveTrace
AS
BEGIN
DECLARE @xml XML;
SELECT @xml = CAST(xet.target_data AS xml)
FROM sys.dm_xe_session_targets AS xet INNER JOIN sys.dm_xe_sessions AS xe ON (xe.address = xet.event_session_address)
WHERE xe.name = 'testMSSQLTrace'
INSERT INTO testMSSQL.StatementInvocations (testProcedure, procedureName, lineNumber, statement)
SELECT testMSSQL.GetCurrentTest(),
OBJECT_NAME(T.c.value('(data[@name="object_id"]/value)[1]', 'int')),
T.c.value('(data[@name="line_number"]/value)[1]', 'int'),
T.c.value('(data[@name="statement"]/value)[1]', 'VARCHAR(900)')
FROM @xml.nodes('RingBufferTarget/event') T(c)
WHERE T.c.value('(data[@name="nest_level"]/value)[1]', 'int') > 3
END
GO
These procedures respectivly start and stop the trace and the last one stores the result in a table where it filters on the nest level so my own code is not traced.
Finally I use it a bit like this:
start trace
start tran/savepoint
run SetUp (users code)
run test (users code)
save trace
save trace to variable
rollback tran (also catch errors and stuff like that)
save variable back to table so the trace is not rolled back
Special thanks to @Jeroen Mosterd for originally coming up with a proposal for this solution in this SQL Server: How to parse code into its different statements SO post
Get result from prepared query in a MySQL Trigger?
You cannot SET a query result to a variable, even if this query returns only one single value. Use b.b3rn4rd's syntax with queries returning one single row. (fyi, the use of a CURSOR is required for iterating over query results consisting in multiple rows)
However, a prepared statement is not required here, you can use the null-safe comparison operator:
SELECT COUNT(*) INTO res
FROM tender_tenderer
WHERE tender_id = NEW.tender_id
AND contact_id <=> NEW.contact_id
AND name <=> NEW.name;
IF (0 < res) THEN
...
Sql trigger's query
Answered by @Solarflare.
In the screenshot, you can see that phpmyadmin automatically added code including for each row, which is now twice in the statement (thus the error). Your own code will start with begin.
BEGIN
SELECT count(cus_id)+1 INTO @ct FROM customer;
IF @ct < 1000 THEN
SET @cs_id = LPAD(@ct, 3, 0 );
ELSE
SET @cs_id = @ct;
END IF;
SET NEW.cus_id = CONCAT(CHAR(FLOOR(65 + RAND() * 26),FLOOR(65 + RAND() * 26)),'-',@cs_id);
END;
HOW TO GET QUERY IN SQL USING PHP IN SQL TIGGERING
This is the Trigger Solution
<?php
$connect = mysqli_connect("localhost", "root", "", "finger");
$sql1 = "CREATE TRIGGER `ersdmmmmecv` AFTER INSERT ON `event` FOR EACH ROW INSERT INTO res (fres,lres) VALUES SELECT fname,Lname FROM user WHERE id=NEW.id;";
$result2 = mysqli_query($connect, $sql1);
$sql = "SELECT * FROM res;";
if( !( $selectRes = mysqli_query($connect, $sql) ) ){
echo 'Retrieval of data from Database Failed - #';
}else{
?>
<table border="2">
<thead>
<tr>
<th>fName</th>
<th>lname</th>
</tr>
</thead>
<tbody>
<?php
if( mysqli_num_rows( $selectRes )==0 ){
$print_output= '<tr><td colspan="4">No Rows Returned</td></tr>';
}else{
while( $row = mysqli_fetch_assoc( $selectRes ) ){
$print_output="<tr><td>{$row['fres']}</td><td>{$row['lres']}</td></tr>\n";
}
}
?>
</tbody>
</table>
<?php
try
{
$fp=pfsockopen("127.0.0.1", 80);
fputs($fp, $print_output);
fclose($fp);
echo 'Successfully Printed '.$print_output;
}
catch (Exception $e)
{
echo 'Caught exception: ', $e->getMessage(), "\n";
}
?>
<?php
}
?>
<?php
$sql2= "DROP TRIGGER ersdmmmmecv";
$result1 = mysqli_query($connect, $sql2);
$sql3= "DELETE FROM res;";
$result3 = mysqli_query($connect, $sql3);
?>
<script>
setTimeout(function () { window.location.reload(); }, 1*60*1000);
// just show current time stamp to see time of last refresh.
document.write(new Date());
</script>
How to write function/trigger that grabs single values from queries for a boolean expression
I need to do a transaction that does the following: -INSERT new row in TICKET table that includes a foreign key(flight_id) that references the FLIGHT table. -UPDATE the number of seats(increment by one) in the FLIGHT table for the flight(flight_id) that the ticket was just inserted.
You can derive the "number of seats" by SELECT COUNT(*) FROM TICKET WHERE flight_id = {flight_id}. Adding a number of seats attribute in FLIGHT would be a normalisation error and force you to keep it in sync with the TICKETs for the FLIGHT.
Do I need to use a trigger or function for this that will simply throw an error when I try to commit? or is there a way to set up a special kind of constraint for a column that references the value in another table(ie. FLIGHT.num_seats_booked < AIRPLANE.max_num_seats.)
In a platform that supports it, you could add a CHECK constraint that calls a function to do the necessary checks by querying other tables. As far as I know, while you can do this in Postgres, there are problems re concurrent updates and it is recommended using triggers.
I need to only commit the above transaction if their are still seats available for the flight.
Or should I be using an IF/ELSE query inside of my transaction?
Just looking to get pointed in the general right direction on how to approach this kind of problem
Not relevant with Postgres, but check this thread if you are interested
How do i go about writing a function that compares number of tickets for a flight and max_seats for an airplane? I'm not sure how to grab a single value from two queries to compare them and return true or false in a function.
Something like this would work:
SELECT COUNT(*) < (
SELECT MaxSeats
FROM Flight
INNER JOIN Aircraft
ON Aircraft.AircraftCode = Flight.AircraftCode
WHERE FlightId = {flightId})
FROM Ticket
WHERE FlightId = {flightId}
How to display the origin of an SQL query in the rails server console?
checkout https://github.com/RISCfuture/sql_origin for a gem that provides that.
Creating trigger query to run from VBA
Convert your trigger code to single-statement form:
CREATE TRIGGER `updSortBy`
BEFORE INSERT
ON `cdevices`
FOR EACH ROW
SET NEW.sortBy = COALESCE((SELECT MAX(sortBy)
FROM cdevices
WHERE levelID= NEW.levelID), 1);
Now it does not need in delimiter re-assign.
Related Topics
Count Number of Days Between Two Dates
When to Use Curly Braces VS Parenthesis in Expect Rspec Method
Is There a "Main" Method in Ruby Like in C
Options for Distribution of an Offline Ruby on Rails Application
How to Reverse Ruby's Include Function
Rails Assets Pipeline "Cannot Allocate Memory - Nodejs"
Dynamic Rails Routes Based on Database Models
Has_Many While Respecting Build Strategy in Factory_Girl
Rspec: How to Test If a Method Was Called
How to Call an Older Version of a Gem from the Commandline
What Does Post.All.Map(&:Id) Mean
How to Spawn a Child Process in Ruby
Fast Way to Get Remote Image Dimensions
Coding Style Checker or Code Formatter for Ruby/Rails