Lesson 8	Modifying triggers
Objective	Practice modifying triggers in SQL-Server

Practice Modifying Triggers in SQL-Server 2022

Earlier in this module, we created the trgSalary trigger to enforce a business rule in which no employee is allowed to have a salary greater than $200,000 unless authorized by the employee's manager (with the manager’s employee ID entered as part of the transaction). The company wishes to change this business rule so that authorization will now be required for any salary greater than $150,000, as opposed to $200,000 (I guess too many people were being paid too much money). Remember that the Employees table contains these columns

Column name	Data type
EmployeeID	`int`
Salary	`smallmoney`
HireDate	`smalldatetime`
ApprovalID	`int`

To modify the trigger that you created earlier in this module, you can use the Transact-SQL code shown in the following SlideShow:

1) Tests whether the data being inserted violates the business rule.

3) Returns an error back to the calling program

ALTER TRIGGER (Transact-SQL)

Modifies the definition of a DML, DDL, or logon trigger that was previously created by the CREATE TRIGGER statement. Triggers are created by using CREATE TRIGGER. They can be created 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. For more information about the parameters that are used in the ALTER TRIGGER statement, see CREATE TRIGGER (Transact-SQL).

ALTER TRIGGER applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database.

-- SQL Server SyntaxTrigger on an INSERT, UPDATE, or DELETE statement to a table or view (DML Trigger)
ALTER TRIGGER schema_name.trigger_name 
ON  ( table | view ) 
[ WITH <dml_trigger_option> [ ,...n ] ]
 ( FOR | AFTER | INSTEAD OF ) 
{ [ DELETE ] [ , ] [ INSERT ] [ , ] [ UPDATE ] } 
[ 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_nameTrigger on a CREATE, 
		ALTER, DROP, GRANT, DENY, REVOKE, or UPDATE statement (DDL Trigger)

ALTER TRIGGER trigger_name 
ON { DATABASE | ALL SERVER } 
[ WITH <ddl_trigger_option> [ ,...n ] ]
{ FOR | AFTER } { event_type [ ,...n ] | event_group } 
AS { sql_statement [ ; ] | EXTERNAL NAME <method specifier> 
[ ; ] }
} 

<ddl_trigger_option> ::=
    [ ENCRYPTION ]
    [ <EXECUTE AS Clause> ]

<method_specifier> ::=
    assembly_name.class_name.method_nameTrigger on a LOGON event (Logon Trigger)
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 ]

<method_specifier> ::=    assembly_name.class_name.method_name

Modifying Triggers - Exericse

Click the Exercise link below to practice modifying triggers.
Modifying Triggers - Exericse
In the next lesson, you will learn how to delete a trigger.