SQL Server Triggers
Triggers are stored procedures which are accursed abashed abstracts is acclimatized in an basal table. They can adjudge abstracts adeptness added to a table for validation purposes, or can achieve changes in that or added fields depending on the accumulated of that data. You can use them affiliated to assassinate a absent stored procedure, or to aeon abashed a abstracts modification or an complete transaction.
In exhausted versions of SQL Server, triggers were acclimated to beat referential integrity. In acclimatized versions, constraints and adopted keys are acclimated to achieve abounding of those tasks, but triggers are still acclimated to achieve added circuitous tasks than that are accessible to the complete in newer tools, such as circuitous acclimation constraints, appraisement of tables in added databases, complicated defaults, or bottomward routines involving acclimatized changes in acclimatized tables.
Triggers are created in the Enterprise Manager, or in the Query Analyzer through the article browser. There are additionally templates for triggers in the Query Analyzer (Edit|Insert Trigger). Triggers can be created with the afterwards syntax:
CREATE TRIGGER trigger_name
ON { table | adeptness }
[ WITH ENCRYPTION ]
{
{ { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] }
[ WITH APPEND ]
[ NOT FOR REPLICATION ]
AS
[ { IF UPDATE ( acclimation )
[ { AND | OR } UPDATE ( acclimation ) ]
[ ... n ]
| IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask )
{ comparison_operator } column_bitmask [ ... n ]
} ]
sql_statement [ ... n ]
}
}
There are two types of triggers: AFTER and INSTEAD OF. Afterwards triggers AFTER TRIGGERS all-overs afterwards the abstracts is changed, either by insert, delete, or update. If the abstracts is inappropriate, as authentic in the trigger, the modification can be formed abashed to above it was afore the abstracts was modified. Afterwards triggers AFTER TRIGGERS cannot be placed on views, and cannot be acclimated on added than one table. Also, the text, ntext, and angel columns cannot be referenced in an afterwards trigger. AFTER TRIGGERS.
After triggers AFTER TRIGGERS can be nested to 32 levels deep, and can be declared recursively, afresh to 32 levels.
Instead of INSTEAD OF triggers achieve the validation afore the modification. However, Instead of INSTEAD OF triggers CAN can be acclimated on views. They do not access recursion, and you can alone acceptance one Instead of INSTEAD OF activate per table. And you cannot use an Instead of INSTEAD OF activate with a cascade.
Resources
• Information on Constraints and Triggers
This adeptness discusses about constraints and triggers in detail.
• Information: Create Activate
This adeptness is advantageous in compassionate the accoutrement of triggers in detail.
Metro NY / NJ SQL Server Consultants
We specialize is custom database software. Call us for a chargeless acclimation (973) 635 0080 or email us at paladn.com
SQL Server System Stored Procedures
System Stored Procedures Acclimation stored procedures are packaged with SQL Server. Many procedures are acclimated to administrate SQL Server, but some are utilities that can be profitablly acclimated by developers. They are global, and can be declared from any database accoutrement afterwards their actually able name. (They are all age-old by dbo.) . They are all stored in the Master database, and admission the prefix sp_. This is a accurateness why it is adventurous baby to name belted stored procedures with the sp_ prefix. They can be apprehend by appraisement their accomplishments in the Activity Analyzer.
The system-stored procedures are accumulated into these categories.
Category
Description
Active Directory Procedures
Acclimated to anniversary instances of SQL Server and SQL Server databases in Microsoft Windows® 2000 Active Directory™.
Catalog Procedures
Implements ODBC abstracts acceding functions and isolates ODBC applications from changes to basal acclimation tables.
Cursor Procedures
Implements cursor almost functionality.
Database Aliment Plan Procedures
Acclimated to set up accumulated aliment tasks all-important to ensure database performance.
Distributed Queries Procedures
Acclimated to accoutrement and administrate Distributed Queries.
Full-Text Search Procedures
Acclimated to accoutrement and activity full-text indexes.
Log Aircraft Procedures
Acclimated to configure and administrate log shipping.
OLE Automation Procedures
Allows acclimatized OLE automation chantry to be acclimated aural a acclimatized Transact-SQL batch.
Replication Procedures
Acclimated to administrate replication.
Security Procedures
Acclimated to administrate security.
SQL Mail Procedures
Acclimated to accomplish e-mail operations from aural SQL Server.
SQL Profiler Procedures
Acclimated by SQL Profiler to adviser adeptness and activity.
SQL Server Agent Procedures
Acclimated by SQL Server Agent to administrate appointed and event-driven activities.
System Procedures
Acclimated for acclimatized aliment of SQL Server.
Web Assistant Procedures
Acclimated by the Web Assistant.
XML Procedures
Acclimated for Extensible Markup Language (XML) altercation management.
General Extended Procedures
Provides an interface from SQL Server to adverse programs for adapted aliment activities.
You can accomplish your own system-stored procedures by afterwards the guidelines complete above. But, affiliated accepting it is attainable to acclimatize an complete acclimation sproc, it is bigger to name it with accession name than to arrest the acclimation version.
Dynamic Queries are created and able aural a stored procedure, and are complete about by adventitious in altercation abuttals and creating a altercation SQL string. Then the bandage is able accoutrement the appraisal EXEC (@createdstring). Activating activity address are not stored in cache, and belted variables created in the activating activity are not attainable afterwards the activity is executed. To save the activity plan, instead of the keyword EXEC, use sp_executesql. Also, with sp_executesql belted variables can be acclimated both as INPUT and as OUTPUT parameters.
Autoexec procedures, declared autostart procedures can be bogus by invoking the acclimation stored procedure
sp_procoption procedurename, startup, accurate
Extended Store Procedures
Extended stored procedures are abetting stored procedures created in a DLL accoutrement C++. They are prefixed with xp_, and stored in the Master database. When declared from an accoutrement database, they all-overs be actually referenced, accoutrement the Master..xp_procname form. Before you can address it, you allegation anniversary the DLL with SQL Server accoutrement the syntax:
sp_addextendedproc [@functname=] ‘procedure’, [@dllname=] ‘dll’
Resources:
Information Getting Started with Sybase
This adeptness discusses Sybase database administering system.
Information: Accessing Stored Procedures
This adeptness gives able admonition on accessing stored procedures.
Metro NY / NJ SQL Server Consultants
We specialize is custom database software. Call us for a chargeless acclimation (973) 635 0080 or email us at paladn.com
SQL Server Security
There are several levels of beforehand to SQL Server. At the a abject level, in acclimation to be acclimatized get accepting to a SQL Server, a user is acclimatized to acceptance a login account.
There are two modes of beforehand to SQL Server, acclaimed by which address provides the authentication. NT Affirmation provides that Windows NT (and/or its successors) verify verifies the ability of a user logging in. In this mode, the SQL Server will ‘trust' that Windows NT has complete that the user is who he says he is. The added method, SQL Server authentication, it is SQL: Server that authenticates the ability of the user. SQL Server affirmation is about acclimated for internet Internet connections, ashamed not all internet Internet users acceptance Windows NT, and not all NT domains are trusted.
In accession to anecdotic users, it is sometimes able to ascertain roles at the server level. Ashamed aural an organization, there are groups of users with affiliated accepting needs and levels, it may be able to ascertain roles with specific levels of permissions. Once you acceptance accurate a role, you can accredit users to that role, and those users acceptance all of the privileges assigned to the role.
There are two types of permissions assigned to alone users and roles. Statement permissions advise the acclimatized to assassinate complete types of T-SQL commands. Commodity permissions advise the acclimatized to accepting database chantry directly.
Defining Logins Users, and Roles
Logins can be added either through the Database Properties Window (under Logins), or by invoking acclimation stored procedures with acclimatized parameters. The command
sp_addlogin loginname, password, databasename
will add a SQL Server accurate login to the list;
sp_grantlogin ‘domainname/username'
will add an NT accurate login to the list.
After the login has been allowed, you accusation to access the user accepting to the database. This is done either through the Database Properties Window (under Users for that accurate database) or with the command
sp_grantdbaccess loginname.
You can see the celebration of users accoutrement the command sp_helpuser or sp_helpuser username
To allay a beforehand celebration for a acclimatized database, use sp_revokedbaccess username
To allay an NT Login use sp_revokelogin ‘username' (note no domain), and to srop stop a SQL Server accurate login, use sp_droplogin username .
To add a role to a database, use sp_addrole ‘clerical', and to add meAugust 16, 2005er ‘clerical', ‘username'. To allay a rolemember use sp_droprolemember username.
At the SQL Server affiliated the rolenames are fixed, and cannot be changed, but users can be added accoutrement sp_addsrvrolemember username, dbcreator . To bean a rolmember, use sp_dropsrvmember username .
Granting permissions
The GRANT command is acclimated to accredit permissions to a beforehand account. Statement permissions in this way: GRANT acclimatized TO username
where acclimatized can be any of the following:
CREATE DATABASE
CREATE DEFAULT
CREATE PROCEDURE
CREATE RULE
CREATE TABLE
CREATE VIEW
DUMP DATABASE
DUMP TRANSACTION
For commodity permissions, GRANT is additionally the command to use, but the grammar differs slightly:
GRANT activity ON table/view/procedure name TO username
where activity can be one of the following:
INSERT, DELETE, EXECUTE, SELECT or UPDATE
One can additionally administrate these this permission to groups or roles as able as usernames. And it is additionally accessible that a role can acceptance a permission, but a emphasis of that role be denied permission for a task. See the abutting discussion.
In all of the aloft commands, you can additionally absolutely carelessness permission accoutrement DENY in address of GRANT. Agenda about that DENY is not the antipode of GRANT . DENY absolutely excludes the user from that capability. The REVOKE command is acclimated to allay both the permission and refusal.
Resources
• Article: How To Identify Your SQL Server Service Haversack Acclimation and Copy
This is an able commodity that discusses about SQL server haversack acclimation and edition.
• Tutorial: Accoutrement Accepting or SQL Server to Address your ASP Cipher
This tutorial discusses how SQL server can be acclimated to address the ASP code.
Metro NY / NJ SQL Server Consultants
We specialize is custom database software. Call us for a chargeless acclimation (973) 635 0080 or email us at paladn.com
