[SqlServer01]

Sometimes we want data in some tables not to be deleted. We can use the following trigger. Createtrigger [dbo]. [trg_del] on [dbo]. LogInfoinsteadofdeleteASBEGINdeclare @ couintselect @ coucount (*) fromdeleted; if (@ cou0) RAISERROR (data cannot be deleted !, 16,1) END

Sometimes we want data in some tables not to be deleted. We can use the following trigger. Create trigger [dbo]. [trg_del] on [dbo]. logInfo instead of delete as begin declare @ cou int select @ cou = count (*) from deleted; if (@ cou0) RAISERROR ('data cannot be deleted! ', 16, 1) END

Sometimes we want data in some tables not to be deleted. We can use the following trigger.

Create trigger [dbo]. [trg_del] on [dbo]. LogInfo
Instead of delete
AS
BEGIN
Declare @ cou int
Select @ cou = count (*) from deleted;
If (@ cou> 0)
RAISERROR ('data cannot be deleted! ', 16, 1)
END

Note that the truncate table LogInfo command will still be correctly executed, because truncate does not trigger or write logs, so it cannot be rolled back. Exercise caution when using it.

Sometimes we need to flexibly specify that some users can delete and modify the data table structure. unspecified users cannot perform these operations. The following triggers can be used.

Create trigger TR_Safety_Table
ON DATABASE
FOR DROP_TABLE, ALTER_TABLE
AS
IF (SUSER_SNAME () <> 'sa ') and (SUSER_SNAME () <> 'test '))
BEGIN
PRINT 'You have no permission to drop table and alter table! '
ROLLBACK
END

Permissions on databases or tables can be controlled by roles and permissions. The following are common basic statements, which are written here for memo.

-- Login, user and permission
-- Delete Logon
-- Exec sp_droplogin 'test'
-- New Login
Exec sp_addlogin @ loginame = n' test ',
@ Passwd = '000000 ',
@ Defdb = n'testdb ',
@ Deflanguage = n'simplified Chinese ',
@ Sid = NULL,
@ Encryptopt = NULL
GO

-- Delete a user
-- Exec sp_dropuser 'test'

Exec sp_adduser 'test'
-- Authorization
Grant select, INSERT, UPDATE, DELETE
ON LogInfo
TO test
-- Revoke permissions
Revoke DELETE on LogInfo from test;
-- Permission denied
Deny DELETE on LogInfo TO test;
-- Grant role Permissions
-- Exec sp_addrolemember 'db _ owner', 'test'
-- List all table names
-- Select name from sysobjects where xtype = 'U' order by name