Getting current SQL statements using ::fn_get_sql()

In SQL Server, getting the current SQL statements that are being executed by the active sessions is quite easy. In prior versions, one either had to make use Enterprise Manager or had to use DBCC INPUTBUFFER (<spid>) in order to get that information. In newer versions, one can just make use of the ::fn_get_sql() function. This was first introduced in SQL Server 2000 and is much better than the DBCC command since there are no text limitations.

Using the cross apply operator that we had discussed in one of our previous blog posts, one can write up a simple script to show all the SQL commands that are being fired by the active sessions on an instance:

SELECT spid, db_name(s.dbid), text
FROM master.dbo.sysprocesses as s
cross apply ::fn_get_sql(s.sql_handle)

Disabling Trigger at Creation time in Oracle 11g

Prior to Oracle 11g, disabling triggers used to be a two step process. Triggers were automatically created in an ‘enabled’ state. To disable it we had to disable it using the DISABLE clause of the ‘ALTER TRIGGER’ or ‘ALTER TABLE ‘ commands.

In Oracle 11g, we can specify ‘DISABLE’ clause in trigger definition it self.  In the absence of the ‘DISABLE’ clause, the trigger is created in an ‘ENABLED’ state. Following is the script to create table and then create trigger in disabled state.

CREATE TABLE TEST
(
COL1 VARCHAR(5) NOT NULL,
COL2 NUMBER(5),
COL3 DATE
);

Let us create a trigger now.

CREATE OR REPLACE TRIGGER TRI_TEST
BEFORE INSERT ON TEST
FOR EACH ROW
DISABLE
BEGIN
:NEW.COL3 := SYSDATE;
END;

Trigger will be created successfully.  We can verify the status of the trigger using data dictionary view user_triggers. Even though trigger is created in a disabled status,  Oracle will make sure that it does not have any compilation errors. Trigger will be compiled with errors if we try to reference non-existent column in the trigger.

This feature can be very useful when we want to enable trigger at later stage for a specific events and as mentioned earlier, we can avoid extra step of altering the trigger after creation.