In computing, a server is a piece of computer hardware or software that provides functionality for other programs or devices, called “clients”. This architecture is called the client–server model.

A server is a computer that serves information to other computers. These computers, called clients, can connect to a server through either a local area network or a wide area network, such as the internet. A server is a vital piece of your IT infrastructure.

SQL Server is a relational database management system, or RDBMS, developed and marketed by Microsoft.

Similar to other RDBMS software, SQL Server is built on top of SQL, a standard programming language for interacting with the relational databases. SQL server is tied to Transact-SQL, or T-SQL, the Microsoft’s implementation of SQL that adds a set of proprietary programming constructs.

SQL Server works exclusively on Windows environment for more than 20 years. In 2016, Microsoft made it available on Linux. SQL Server 2017 became generally available in October 2016 that ran on both Windows and Linux.

SQL Server Architecture

The following diagram illustrates the architecture of the SQL Server:

SQL Server consists of two main components:

1. Database Engine
2. SQLOS

Database Engine

The core component of the SQL Server is the Database Engine. The Database Engine consists of a relational engine that processes queries and a storage engine that manages database files, pages, pages, index, etc. The database objects such as stored procedures, views, and triggers are also created and executed by the Database Engine.

Relational Engine

The Relational Engine contains the components that determine the best way to execute a query. The relational engine is also known as the query processor.

The relational engine requests data from the storage engine based on the input query and processed the results.

Some tasks of the relational engine include querying processing, memory management, thread and task management, buffer management, and distributed query processing.

Storage Engine

The storage engine is in charge of storage and retrieval of data from the storage systems such as disks and SAN.

SQLOS

Under the relational engine and storage engine is the SQL Server Operating System or SQLOS.

SQLOS provides many operating system services such as memory and I/O management. Other services include exception handling and synchronization services.

SQL Server Services and Tools

Microsoft provides both data management and business intelligence (BI) tools and services together with SQL Server.

For data management, SQL Server includes SQL Server Integration Services (SSIS), SQL Server Data Quality Services, and SQL Server Master Data Services. To develop databases, SQL Server provides SQL Server Data tools; and to manage, deploy, and monitor databases SQL Server has SQL Server Management Studio (SSMS).

For data analysis, SQL Server offers SQL Server Analysis Services (SSAS). SQL Server Reporting Services (SSRS) provides reports and visualization of data. The Machine Learning Services technology appeared first in SQL Server 2016 which was renamed from the R Services.

SQL Server Editions

SQL Server has four primary editions that have different bundled services and tools. Two editions are available free of charge:

SQL Server Developer edition for use in database development and testing.

SQL Server Expression for small databases with the size up to 10 GB of disk storage capacity.

For larger and more critical applications, SQL Server offers the Enterprise edition that includes all SQL server’s features.

SQL Server Standard Edition has partial feature sets of the Enterprise Edition and limits on the Server regarding the numbers of processor core and memory that can be configured.

For the detailed information on the SQL Editions, check it out the available Server Server 2017 Editions.

In this tutorial, you have a brief overview of the SQL Servers including its architecture, services, tools, and editions.

Microsoft SQL Server is a relational database management system developed by Microsoft. As a database server, it is a software product with the primary function of storing and retrieving data as requested by other software applications—which may run either on the same computer or on another computer across a network. 

WHAT ARE TRIGGERS IN SQL SERVER?

A trigger is a special type of stored procedure that automatically runs when an event occurs in the database server. DML triggers run when a user tries to modify data through a data manipulation language (DML) event. DML events are INSERT, UPDATE, or DELETE statements on a table or view. These triggers fire when any valid event fires, whether table rows are affected or not. For more information, see DML Triggers.

DDL triggers run in response to a variety of data definition language (DDL) events. These events primarily correspond to Transact-SQL CREATE, ALTER, and DROP statements, and certain system stored procedures that perform DDL-like operations.

Logon triggers fire in response to the LOGON event that’s raised when a user’s session is being established. You can create triggers directly from Transact-SQL statements or from methods of assemblies that are created in the Microsoft .NET Framework common language runtime (CLR) and uploaded to an instance of SQL Server. SQL Server lets you create multiple triggers for any specific statement.

TYPES OF TRIGGERS

Types of SQL Triggers

These are – INSERT, UPDATE, and DELETE. DDL (data definition language) triggers – As expected, triggers of this type shall react to DDL commands like – CREATE, ALTER, and DROP. Logon triggers – The name says it all. This type reacts to LOGON events.

SQL Server Syntax

-- SQL Server Syntax  
-- Trigger on an INSERT, UPDATE, or DELETE statement to a table or view (DML Trigger)  
  
CREATE [ OR ALTER ] TRIGGER [ schema_name . ]trigger_name   
ON { table | view }   
[ WITH <dml_trigger_option> [ ,...n ] ]  
{ FOR | AFTER | INSTEAD OF }   
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }   
[ WITH APPEND ]  
[ NOT FOR REPLICATION ]   
AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME <method specifier [ ; ] > }  
  
<dml_trigger_option> ::=  
    [ ENCRYPTION ]  
    [ EXECUTE AS Clause ]  
  
<method_specifier> ::=  
    assembly_name.class_name.method_name  
  

-- SQL Server Syntax  
-- Trigger on an INSERT, UPDATE, or DELETE statement to a 
-- table (DML Trigger on memory-optimized tables)  
  
CREATE [ OR ALTER ] TRIGGER [ schema_name . ]trigger_name   
ON { table }   
[ WITH <dml_trigger_option> [ ,...n ] ]  
{ FOR | AFTER }   
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }   
AS { sql_statement  [ ; ] [ ,...n ] }  
  
<dml_trigger_option> ::=  
    [ NATIVE_COMPILATION ]  
    [ SCHEMABINDING ]  
    [ EXECUTE AS Clause ]  
  

-- Trigger on a CREATE, ALTER, DROP, GRANT, DENY, 
-- REVOKE or UPDATE statement (DDL Trigger)  
  
CREATE [ OR ALTER ] TRIGGER trigger_name   
ON { ALL SERVER | DATABASE }   
[ WITH <ddl_trigger_option> [ ,...n ] ]  
{ FOR | AFTER } { event_type | event_group } [ ,...n ]  
AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier >  [ ; ] }  
  
<ddl_trigger_option> ::=  
    [ ENCRYPTION ]  
    [ EXECUTE AS Clause ]  
  

syntaxsqlCopy

-- Trigger on a LOGON event (Logon Trigger)  
  
CREATE [ OR ALTER ] TRIGGER trigger_name   
ON ALL SERVER   
[ WITH <logon_trigger_option> [ ,...n ] ]  
{ FOR| AFTER } LOGON    
AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier >  [ ; ] }  
  
<logon_trigger_option> ::=  
    [ ENCRYPTION ]  
    [ EXECUTE AS Clause ]  
  Remarks for DML Triggers
DML triggers are frequently used for enforcing business rules and data integrity. SQL Server provides declarative referential integrity (DRI) through the ALTER TABLE and CREATE TABLE statements. However, DRI doesn't provide cross-database referential integrity. Referential integrity refers to the rules about the relationships between the primary and foreign keys of tables. To enforce referential integrity, use the PRIMARY KEY and FOREIGN KEY constraints in ALTER TABLE and CREATE TABLE. If constraints exist on the trigger table, they're checked after the INSTEAD OF trigger runs and before the AFTER trigger runs. If the constraints are violated, the INSTEAD OF trigger actions are rolled back and the AFTER trigger isn't fired.

You can specify the first and last AFTER triggers to be run on a table by using sp_settriggerorder. You can specify only one first and one last AFTER trigger for each INSERT, UPDATE, and DELETE operation on a table. If there are other AFTER triggers on the same table, they're randomly run.

If an ALTER TRIGGER statement changes a first or last trigger, the first or last attribute set on the modified trigger is dropped, and you must reset the order value by using sp_settriggerorder.

An AFTER trigger is run only after the triggering SQL statement has run successfully. This successful execution includes all referential cascade actions and constraint checks associated with the object updated or deleted. An AFTER does not recursively fire an INSTEAD OF trigger on the same table.

If an INSTEAD OF trigger defined on a table runs a statement against the table that would ordinarily fire the INSTEAD OF trigger again, the trigger isn't called recursively. Instead, the statement processes as if the table had no INSTEAD OF trigger and starts the chain of constraint operations and AFTER trigger executions. For example, if a trigger is defined as an INSTEAD OF INSERT trigger for a table. And, the trigger runs an INSERT statement on the same table, the INSERT statement launched by the INSTEAD OF trigger doesn't call the trigger again. The INSERT launched by the trigger starts the process of running constraint actions and firing any AFTER INSERT triggers defined for the table.

When an INSTEAD OF trigger defined on a view runs a statement against the view that would ordinarily fire the INSTEAD OF trigger again, it's not called recursively. Instead, the statement is resolved as modifications against the base tables underlying the view. In this case, the view definition must meet all the restrictions for an updatable view. For a definition of updatable views, see Modify Data Through a View.

For example, if a trigger is defined as an INSTEAD OF UPDATE trigger for a view. And, the trigger runs an UPDATE statement referencing the same view, the UPDATE statement launched by the INSTEAD OF trigger doesn't call the trigger again. The UPDATE launched by the trigger is processed against the view as if the view didn't have an INSTEAD OF trigger. The columns changed by the UPDATE must be resolved to a single base table. Each modification to an underlying base table starts the chain of applying constraints and firing AFTER triggers defined for the table.
Trigger Limitations
CREATE TRIGGER must be the first statement in the batch and can apply to only one table.

A trigger is created only in the current database; however, a trigger can reference objects outside the current database.

If the trigger schema name is specified to qualify the trigger, qualify the table name in the same way.

The same trigger action can be defined for more than one user action (for example, INSERT and UPDATE) in the same CREATE TRIGGER statement.

INSTEAD OF DELETE/UPDATE triggers can't be defined on a table that has a foreign key with a cascade on DELETE/UPDATE action defined.

Any SET statement can be specified inside a trigger. The SET option selected remains in effect during the execution of the trigger and then reverts to its former setting.

When a trigger fires, results are returned to the calling application, just like with stored procedures. To prevent results being returned to an application because of a trigger firing, don't include either SELECT statements that return results or statements that carry out variable assignment in a trigger. A trigger that includes either SELECT statements that return results to the user or statements that do variable assignment, requires special handling. You'd have to write the returned results into every application in which modifications to the trigger table are allowed. If variable assignment must occur in a trigger, use a SET NOCOUNT statement at the start of the trigger to prevent the return of any result sets.

Although a TRUNCATE TABLE statement is in effect a DELETE statement, it doesn't activate a trigger because the operation doesn't log individual row deletions. However, only those users with permissions to run a TRUNCATE TABLE statement need be concerned about inadvertently circumventing a DELETE trigger this way.

The WRITETEXT statement, whether logged or unlogged, doesn't activate a trigger.

The following Transact-SQL statements aren't allowed in a DML trigger:

ALTER DATABASE
CREATE DATABASE
DROP DATABASE
RESTORE DATABASE
RESTORE LOG
RECONFIGURE
Additionally, the following Transact-SQL statements aren't allowed inside the body of a DML trigger when it's used against the table or view that's the target of the triggering action.

CREATE INDEX (including CREATE SPATIAL INDEX and CREATE XML INDEX)
ALTER INDEX
DROP INDEX
DROP TABLE
DBCC DBREINDEX
ALTER PARTITION FUNCTION
ALTER TABLE when used to do the following:
Add, modify, or drop columns.
Switch partitions.
Add or drop PRIMARY KEY or UNIQUE constraints.
Drop your comment
drop your comment


Drop Your comment