What is the difference between trigger and constraints ?

Questions by narasingha baral

Showing Answers 1 - 21 of 21 Answers

Rushil

  • Sep 17th, 2014
 

Trigger can be use to create user defined constraints. This is one difference wrt constraints. It has many other benefits also.
Thanks

  Was this answer useful?  Yes

binita

  • Sep 24th, 2014
 

Trigger is a stored routine.it is a set of SQL statements used for protecting the integrity of data.But constraint is used to define rules to allow or restrict what values can be stored in columns. The purpose of inducing constraints is to enforce integrity of database.it restricts the insertion of data in a table.

  Was this answer useful?  Yes

Vaithy

  • Oct 11th, 2014
 

1. Trigger can be on update/delete statements also, but constraints are related to provide restrictions while inserting the data.
2. Triggers can be defined at system level(Database shutdown, log in, etc), but constraints cant be used at system level.
3. We can use anonymous transactions on trigger, but we cant use anonymous transactions on constraints.

  Was this answer useful?  Yes

Samudrala

  • Jan 30th, 2015
 

Triggers are stored programs, which are automatically executed or fired when some events occur. Triggers are, in fact, written to be executed in response to any of the following events:
A database manipulation (DML) statement (DELETE, INSERT, or UPDATE).

A database definition (DDL) statement (CREATE, ALTER, or DROP).

A database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN).

Triggers could be defined on the table, view, schema, or database with which the event is associated.
Syntax:

Code
  1. CREATE [OR REPLACE ] TRIGGER trigger_name

  2. {BEFORE | AFTER | INSTEAD OF }

  3. {INSERT [OR] | UPDATE [OR] | DELETE}

  4. [OF col_name]

  5. ON table_name

  6. [REFERENCING OLD AS o NEW AS n]

  7. [FOR EACH ROW]

  8. WHEN (condition)  

  9. DECLARE

  10.    Declaration-statements

  11. BEGIN

  12.    Executable-statements

  13. EXCEPTION

  14.    Exception-handling-statements

  15. END;


  Was this answer useful?  Yes

Samudrala

  • Jan 30th, 2015
 

Constraints are the rules enforced on data columns on table. These are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the database. Contraints could be column level or table level. Column level constraints are applied only to one column where as table level constraints are applied to the whole table.

Following are commonly used constraints available in SQL:

 NOT NULL Constraint: Ensures that a column cannot have NULL value.

 DEFAULT Constraint: Provides a default value for a column when none is specified.

 UNIQUE Constraint: Ensures that all values in a column are different.

 PRIMARY Key: Uniquely identified each rows/records in a database table.

 FOREIGN Key: Uniquely identified a row/record in any other database table.

 CHECK Constraint: The CHECK constraint ensures that all values in a column satisfy certain conditions.

 INDEX: Use to create and retrieve data from the database very quickly.

  Was this answer useful?  Yes

munny

  • Jul 24th, 2015
 

Triggers are the stored procedures that are fired when an DB event occur where as Constraints are the checks or the rules that are enforced on data columns to maintain the data integrity. Both can be use for the data integrity. Constraints are used at the data creation level. With Triggers we can define the some more requisite action like data messaging, data population) on other relation tables

  Was this answer useful?  Yes

kishanjha

  • Feb 26th, 2017
 

Trigger are different from Constraints.
Both supports data integrity but trigger can be imposed on more than one tables.
Triggers can be used to take table backups and changes logs. Like what was the old value, who updated (user) and when (update time)

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions