@@IDENTITY vs. SCOPE_IDENTITY

Both @@IDENTITY and SCOPE_IDENTITY() return the last identity value (primary key) that was entered by your active session, but in different scenarios they can each return different values. SCOPE_IDENTITY and @@IDENTITY return the last identity values that are generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope; @@IDENTITY is not limited to a specific scope. Active session is the current activity you are engaging in. For example, if you can a stored procedure, that is what I am referring to as your active session. Each call to t a stored procedure (or user defined function, etc) is a session, unless the a stored procedure is nested in the stored procedure you are calling. In the case of a nested stored procedure or user defined method, while they are separate methods, they are part of the current session, but not part of the current scope. Your scope is limited to the method (stored procedure or user defined function) that you explicitly invoked. This is where the difference between @@IDENTITY and SCOPE_IDENTITY() comes in.
@@IDENTITY will return the last identity value entered into a table in your current session (this is limited to your session only, so you won't get identities entered by other users). While @@IDENTITY is limited to the current session, it is not limited to the current scope. In other words, if you have a trigger on a table that causes an identity to be created in another table, you will get the identity that was created last, even if it was the trigger that created it. Now this isn't bad, as long as you ensure that things are done in the correct order. Where this can get ugly is when there is an application revision and a new trigger gets added that gets fired from your stored procedure. Your code didn't anticipate this new trigger, so you could now be getting an incorrect value back.
SCOPE_IDENTITY(), like @@IDENTITY, will return the last identity value created in the current session, but it will also limit it to your current scope as well. In other words, it will return the last identity value that you explicitly created, rather than any identity that was created by a trigger or a user defined function.

For example, there are two tables, T1 and T2, and an INSERT trigger is defined on T1. When a row is inserted to T1, the trigger fires and inserts a row in T2. This scenario illustrates two scopes: the insert on T1, and the insert on T2 by the trigger.
Assuming that both T1 and T2 have identity columns, @@IDENTITY and SCOPE_IDENTITY will return different values at the end of an INSERT statement on T1. @@IDENTITY will return the last identity column value inserted across any scope in the current session. This is the value inserted in T2. SCOPE_IDENTITY() will return the IDENTITY value inserted in T1. This was the last insert that occurred in the same scope. The SCOPE_IDENTITY() function will return the null value if the function is invoked before any INSERT statements into an identity column occur in the scope.
@@IDENTITY returns the most recently created identity for your current connection, not necessarily the identity for the recently added row in a table. Always use SCOPE_IDENTITY() to return the identity of the recently added row.

Mutating Tables

OK, so you've just recieved the error:
ORA-04091: table XXXX is mutating, trigger/function may not see it

What is mutating table?
A mutating table is a table that is currently being modified by an update, delete, or insert statement. For example, if your trigger contains a select statement or an update statement referencing the table it is triggering off of you will receive the error. Another way this error can occur is if the trigger has statements to change the primary, foreign or unique key
columns of the table the trigger is triggering off of.
Mutatation of table is a state of table when in before /after update trigger table tries to update the same table.As the call of update also calls same trigger then the trigger will be called infinite times. then the table will be mutating .

When mutating table error occurs?
I have a table containing a key/status/effective date combination. When status changes, the values are propagated by trigger to a log table recording the status history. When no RI constraint is in place everything works fine.
When an RI trigger enforces a parent-child relationship, the status change logging trigger fails because the parent table is mutating. Propagating the values to the log table implicitly generates a lookup back to the parent table to ensure the RI constraint is satisfied.

Fix Oracle mutating trigger table errors:
Don't use triggers - The best way to avoid the mutating table error is not to use triggers. While the object-oriented Oracle provides "methods" that are associated with tables, most savvy PL/SQL developers avoid triggers unless absolutely necessary.
Use an "after" trigger - If you must use a trigger, it's best to avoid the mutating table error by using an "after" trigger, to avoid the currency issues associated with a mutating table. For example, using a trigger ":after update on xxx", the original update has completed and the table will not be mutating.
Re-work the trigger syntax – Find other ways to avoid mutating tables with a combination of row-level and statement-level triggers.
Use autonomous transactions - You can avoid the mutating table error by marking your trigger as an autonomous transaction, making it independent from the table that calls the procedure.

Retrieving SQL Server Database Properties with DATABASEPROPERTYEX

SQL Server 2000 and 2005 have a built-in function called DATABASEPROPERTYEX that allows you to return the specific information you are looking for, for one or all databases. This function can be called from a SELECT statement to return the results of one or more databases.
Syntax :
DATABASEPROPERTYEX ( database , property )
e.g.
Retrieving the status of the AUTO_SHRINK database option:
SELECT DATABASEPROPERTYEX('AdventureWorks', 'IsAutoShrink');
Retrieving the default collation for a database:
SELECT DATABASEPROPERTYEX('AdventureWorks', 'Collation');

We can use DATABASEPROPERTYEX for Collecting SQL Server database usage information for free space and trending as follows:

DBA need to monitor the following database/database file level metrics:
Server/Instance Name
Database Name
Database File Names (both logical and full physical path)
File Size (In Megabytes)
Database Status
Recovery Mode
Free Space (In Megabytes and Percent)

Script:

DECLARE @DBInfo TABLE ( ServerName VARCHAR(100), DatabaseName VARCHAR(100), FileSizeMB INT, LogicalFileName sysname, PhysicalFileName NVARCHAR(520), Status sysname, Updateability sysname, RecoveryMode sysname, FreeSpaceMB INT, FreeSpacePct VARCHAR(7), FreeSpacePages INT, PollDate datetime)

DECLARE @command VARCHAR(5000)
SELECT @command = 'Use [' + '?' + '] SELECT @@servername as ServerName, ' + '''' + '?' + '''' + ' AS DatabaseName, CAST(sysfiles.size/128.0 AS int) AS FileSize, sysfiles.name AS LogicalFileName, sysfiles.filename AS PhysicalFileName, CONVERT(sysname,DatabasePropertyEx(''?'',''Status'')) AS Status, CONVERT(sysname,DatabasePropertyEx(''?'',''Updateability'')) AS Updateability, CONVERT(sysname,DatabasePropertyEx(''?'',''Recovery'')) AS RecoveryMode, CAST(sysfiles.size/128.0 - CAST(FILEPROPERTY(sysfiles.name, ' + '''' + 'SpaceUsed' + '''' + ' ) AS int)/128.0 AS int) AS FreeSpaceMB, CAST(100 * (CAST (((sysfiles.size/128.0 -CAST(FILEPROPERTY(sysfiles.name, ' + '''' + 'SpaceUsed' + '''' + ' ) AS int)/128.0)/(sysfiles.size/128.0)) AS decimal(4,2))) AS varchar(8)) + ' + '''' + '%' + '''' + ' AS FreeSpacePct, GETDATE() as PollDate FROM dbo.sysfiles'

INSERT INTO @DBInfo (ServerName, DatabaseName, FileSizeMB, LogicalFileName, PhysicalFileName, Status, Updateability, RecoveryMode, FreeSpaceMB, FreeSpacePct, PollDate) EXEC sp_MSForEachDB @command SELECT ServerName, DatabaseName, FileSizeMB, LogicalFileName, PhysicalFileName, Status, Updateability, RecoveryMode, FreeSpaceMB, FreeSpacePct, PollDate FROM @DBInfo ORDER BY ServerName, DatabaseName

Remark:

CONVERT(sysname,DatabasePropertyEx(''?'',''Status'')) AS StatusCONVERT(sysname,DatabasePropertyEx(''?'',''Updateability'')) AS UpdateabilityCONVERT(sysname,DatabasePropertyEx(''?'',''Recovery'')) AS RecoveryMode

The DatabasePropertyEx() function returns the value for the database property or option specified. In this case we are interested in the Status, Updateability, and Recover database option values. I convert the values to the sysname datatype for compatibility with my metadata repository.