SQL CREATE trigger and get the value just inserted (1/2)

SQL CREATE trigger and get the value just inserted

CREATE TRIGGER [t1_test] on [dbo]. [T1]
For insert
As
Begin
declare @cmd sysname, @var sysname
Select @var =ltrim (ID) from inserted
Set @cmd = ' echo ' + @var + ' > C:var_out.txt '
EXEC master.. xp_cmdshell @cmd

End

CREATE trigger Afterdeleteteacher (trigger name)
On teacher (table name corresponding to trigger)
After delete,update (triggered when the table occurs, this is triggered after delete,update)
As
Begin
Specific actions after the trigger
If Update (COURSE_NAME)//If course_name field is updated
Bedin
Related Action statements for table lesson
End
Delete operation
Delete from lesson where course_name= (select Course_name from deleted)

End

Let's look at a simple example.

Create Trigger Trigger_course_name
On teacher
For update
As
If Update (course_name)
Update lesson Set course_name= (select Courses_name from inserted) [color= #ff0000]where course_name= (select Courses_name from deleted) [/color]

About MSSQL triggers description

Create TRIGGER Trigger_name
on {table | View}
[WITH Encryption]
{
{for |   After | Instead of} {[insert] [,] [UPDATE]}
[With Append]
[Not FOR replication]
As
[{If Update (column)
[{and | or} update (column)]
[... n]
| if (columns_updated () {bitwise_operator} updated_bitmask)
{comparison_operator} Column_bitmask [... n]
}   ]
Sql_statement [... n]
}
}

Parameters
Trigger_name

is the name of the trigger. Trigger names must conform to the rules for identifiers and must be unique in the database tutorial. You can choose whether to specify the trigger owner name.

Table | View

is the table or view on which the trigger is executed, sometimes referred to as a trigger table or a trigger view. You can choose whether to specify the owner name of the table or view.

With encryption

Encrypt an entry in the syscomments table that contains the text of the CREATE TRIGGER statement. Use with encryption to prevent triggers from being published as part of SQL Server replication.

After

Specifies that the trigger fires only if all the actions specified in the triggering SQL statement have been successfully executed. All referential cascade operations and constraint checks must also be completed successfully before this trigger can be executed.

If you specify only the FOR keyword, after is the default setting.

After triggers cannot be defined on the view.

Instead of

Specifies that the trigger is executed instead of executing the triggering SQL statement, thus replacing the action of the triggering statement.

On a table or view, each insert, UPDATE, or DELETE statement can define a instead of trigger. However, you can define a view on each view that has a instead of triggers.

Instead OF triggers cannot be defined on updatable views with CHECK option. If you add an instead of trigger to an updatable view that specifies the WITH CHECK option option, SQL Server generates an error. The user must delete this option with ALTER view to define the instead of trigger.

{[delete] [,] [insert] [,] [UPDATE]}

is a keyword that specifies which data modification statements to execute on a table or view that will activate the trigger. At least one option must be specified. These keywords are allowed to be combined in any order in the trigger definition. If you specify more than one option, separate the options with commas.

For instead OF triggers, the delete option is not allowed on a table with the ON DELETE Cascade action reference relationship. Similarly, the update option is not allowed on a table with an ON UPDATE Cascade action reference relationship.

Home 1 2 last page