CROSS APPLY
Here is another nice one (CROSS APPLY) and also note the key features here:
Create Function GetOrderDetails(@OrderId Int, @count Int)
returns table
As
RETURN
Select top(@count) * from [Order Details]
Where OrderId = @OrderId
In the above example note the usage of @count in top. I just wanted to show the output of the following statement using the CROSS APPLY statement:
Select * from Orders
Inner join [Order Details] on Orders.OrderId = [order details].OrderId
Check this:
Select O.OrderId, OrderDate, ProductId, UnitPrice, Quantity from orders O
CROSS APPLY
dbo.GetOrderDetails(O.OrderID, 3) AS D
ORDER BY
O.OrderId Asc
This statement returns all the Orders and only the first three detail records. The APPLY clause acts like a JOIN without the ON clause. Refer to both CROSS APPLY and OUTER Apply here.
Event Notification and DDL Triggers
This function called Event Notifications is a feature that can be used by applications using SQL Server. This Event notifications executes in response to a variety of Transact-SQL data definition language (DDL) statements and SQL Trace events by sending information about these events to a Service Broker service.
A sample event will be like this:
CREATE EVENT NOTIFICATION log_ddl1
ON SERVER
FOR ALTER_TABLE
TO SERVICE ‘//northwind.com/archiveservice’ , ‘current database’;
In the above example, ALTER_TABLE is a DDL statement. Refer to DDL Events for Use with Event Notifications for a complete list of DDL Events.
Similarly DDL Triggers are a special kind of trigger that fire in response to Data Definition Language (DDL) statements. They can be used to perform administrative tasks in the database such as auditing and regulating database operations. The old DML triggers operate on INSERT, UPDATE, and DELETE statements, and help to enforce business rules and extend data integrity when data is modified in tables or views. DDL triggers operate on CREATE, ALTER, DROP, and other DDL statements. They are used to perform administrative tasks and enforce business rules that affect databases. They apply to all commands of a single type across a database, or across a server.
USE Northwind;
GO
IF EXISTS (SELECT * FROM sys.triggers
WHERE parent_class = 0 AND name = ‘safety’)
DROP TRIGGER safety
ON DATABASE;
GO
CREATE TRIGGER safety
ON DATABASE
FOR DROP_SYNONYM
AS
RAISERROR (‘You must disable Trigger “safety” to drop synonyms!’,10, 1)
ROLLBACK
GO
DROP TRIGGER safety
ON DATABASE;
GO
Error Handling
Simply anything can be handled with the TRY/CATCH:
BEGIN TRY
Select 1/0
Select Cast(‘a’ as DateTime)
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() as ErrorNumber,
ERROR_MESSAGE() as ErrorMessage;
END CATCH;
These statements will show the following errors.
And there is more information about the error also:
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() as ErrorState,
ERROR_LINE () as ErrorLine,
ERROR_PROCEDURE() as ErrorProcedure,
ERROR_MESSAGE() as ErrorMessage;